ansaurus

Question

MS Access: searching a column for a star/asterisk

Answer 1

+2 A:

yay, found it out by myself:

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

Fuxi 2009-12-13 06:52:15

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

astander 2009-12-13 07:01:15

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

p.campbell 2009-12-13 07:03:38

Yes, it should.

astander 2009-12-13 07:06:00

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

David-W-Fenton 2009-12-14 22:32:14

"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 2009-12-15 09:51:05

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 2009-12-15 23:48:57

@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 2009-12-16 09:45:16

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 2009-12-16 10:02:20

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

onedaywhen 2009-12-16 10:04:24

Answer 2

+2 A:

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.

astander 2009-12-13 06:55:08

Answer 3

A:

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

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

Remou 2009-12-13 09:47:44

Answer 4

A:

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)

onedaywhen 2009-12-14 08:47:02

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 2009-12-14 22:35:26

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 2009-12-15 09:36:26

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 2009-12-15 23:52:23

@David W. Fenton -- nice work :)

onedaywhen 2009-12-16 09:03:54

ansaurus

tags:

views:

answers:

MS Access: searching a column for a star/asterisk

related questions