2

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?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Fuxi
  • 7,611
  • 25
  • 93
  • 139

4 Answers4

8

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

select * from users where pattern like "*[*]*"
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
2

yay, found it out by myself:

select * from users where instr(pattern,chr(42))
Fuxi
  • 7,611
  • 25
  • 93
  • 139
  • In which version af access did you test this? Does not work in 2007 – Adriaan Stander Dec 13 '09 at 07:01
  • Should it have a boolean condition though - something like `where instr(pattern,chr(42)) > 0` ? – p.campbell Dec 13 '09 at 07:03
  • Technically speaking, all non-zero values should return TRUE, but I always specify since I think it's clearer. – David-W-Fenton Dec 14 '09 at 22:32
  • "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 ;) – onedaywhen Dec 15 '09 at 09:51
  • 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 Dec 15 '09 at 23:48
  • @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: http://www.dbazine.com/ofinterest/oi-articles/celko3 – onedaywhen Dec 16 '09 at 09:45
  • 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 Dec 16 '09 at 10:02
  • ...doesn't conform to Standards and is largely undocumented by the Access Team makes it hard to have a discussion on :( – onedaywhen Dec 16 '09 at 10:04
2

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
2

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)

Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • 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. – David-W-Fenton Dec 14 '09 at 22:35
  • 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. http://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign/720896#720896 – onedaywhen Dec 15 '09 at 09:36
  • 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. – David-W-Fenton Dec 15 '09 at 23:52