MS Access: searching a column for a star/asterisk

Question

I'm looking for a way to search a column of string datatype which contains a * - the problem is that the star or asterisk is a reserved symbol. The following query doesn't work properly:

select * from users where instr(pattern,"*")

How can you write an Access query to search a column for an asterisk?

Solution 1

You can search for reseverd charaters in Access by using square brackets:

select * from users where pattern like "*[*]*"

Solution 2

yay, found it out by myself:

select * from users where instr(pattern,chr(42))

Solution 3

Just use

select * from users where instr(pattern,"*") > 0

From Access: Instr Function

In Access, the Instr function returns the position of the first occurrence of a string in another string.

Solution 4

Use the ALIKE function because its wildcard characters do not include * e.g.

SELECT * FROM Users WHERE pattern ALIKE '%*%';

(Edit by DWF: see @onedayone's useful explanation of ALIKE)

View more solutions

Comments

[Adriaan Stander]

In which version af access did you test this? Does not work in 2007

[p.campbell]

Should it have a boolean condition though - something like where instr(pattern,chr(42)) > 0 ?

[David-W-Fenton]

Technically speaking, all non-zero values should return TRUE, but I always specify since I think it's clearer.

[David-W-Fenton]

Care to draw out the difference between the LIKE and ALIKE operators? I'm unable with some quick searching in Access help to find the distinction. So far as I can tell from experimenting, ALIKE allows the use of SQL Server-compatible wildcards without needing to set your database to use SQL 92 mode.

[onedaywhen]

I usually do but the differences aren't relevant here: all you need to know is that ALIKE treats the * character as a literal. For more details see me other answers e.g. stackoverflow.com/questions/719115/…

[onedaywhen]

"Technically speaking, all non-zero values should return TRUE" -- Perhaps we need a name for someone who doesn't understand the NULL value in SQL ;)

[David-W-Fenton]

Null is not a value, so my statement is correct as it stands (it seems to me that you assuming Null is a "value" (as opposed to simply UNKNOWN, and thus not subject to comparison) is a demonstration of not understanding Null in SQL). But you do make a point that if you pass Null to the InStr() function, you get back a Null, and rows where InStr(pattern, Chr(42)) evaluates to Null will not be returned. It seems to me that since a Null field can't actually contain Chr(42) that this would be an expected (and desirable) result.

[David-W-Fenton]

I edited into your answer the reference to your other answer (which I'd already seen earlier). See, I will edit when I think it's appropriate.

[onedaywhen]

@David W. Fenton: "Null is not a value" -- oh dear. Have you not heard the expression "the NULL value"? I use it all the time! In Standard SQL and early relational theory NULL is a data value and UNKNOWN is a logical value (three valued logic, that is). NULL and UNKNOWN are not the same! Here's some reading for you: dbazine.com/ofinterest/oi-articles/celko3

[onedaywhen]

In Standard SQL, the predicate NULL LIKE '%*%' would evaluate to UNKNOWN (being a logical value). However, for the Access Database Engine treats the result as NULL (being a data value) e.g. SELECT DISTINCT INSTR(NULL, CHR(42)) FROM Calendar; returns the NULL value. Another example of how the Access Database Engine's implementation of three valued logic does not conform to Standards is that you can't cast NULL to a data type e.g. SELECT DISTINCT CLNG(NULL) FROM Calendar; causes an error.

[onedaywhen]

...doesn't conform to Standards and is largely undocumented by the Access Team makes it hard to have a discussion on :(