Use * as character, not as wildcard

[Matthew Pfluger]

I have an unusual situation. I want to write a SQL Criteria statement that
filters out all textual records that contain a "*". That is, many records
look like this:

Alpha Name
**DO NOT USE!!!**
**DO NOT USE!!!**
Joe's Plumbing

I want to filter out all records that contain an asterisk. How can I do
this? Should I not use the LIKE operator?

Thanks,
Matthew Pfluger

 

[Sylvain Lafontaine]

For Access JET or SQL-Server? On SQL-Server, the wildcard character is not
the asterix (*) but the percent symbol %; so I suppose that you mean Access
JET here. For Access, use the list symbol if you want to search for * as an
ordinary character (when not using the ANSI-92 compatible syntax):

.... Where Texte1 Like "*[*]*"


See http://articles.techrepublic.com.com/5100-10878_11-6154704.html for
other tips on using the LIKE operator for Access. Other possibilities would
be to use the ALIKE operator - which always use the ANSI-92 specification
instead of the ANSI-89 - or to use a string searching function:

.... WHERE Text1 ALike "%*%"

.... WHERE InStr (Texte1, "*") > 0

.... WHERE len (Texte1) <> len (Replace (Texte1 , "*", ""))

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Matthew Pfluger]

Awesome! Thanks very much!

Matthew Pfluger

For Access JET or SQL-Server? On SQL-Server, the wildcard character is not
the asterix (*) but the percent symbol %; so I suppose that you mean Access
JET here. For Access, use the list symbol if you want to search for * as an
ordinary character (when not using the ANSI-92 compatible syntax):

.... Where Texte1 Like "*[*]*"


See http://articles.techrepublic.com.com/5100-10878_11-6154704.html for
other tips on using the LIKE operator for Access. Other possibilities would
be to use the ALIKE operator - which always use the ANSI-92 specification
instead of the ANSI-89 - or to use a string searching function:

.... WHERE Text1 ALike "%*%"

.... WHERE InStr (Texte1, "*") > 0

.... WHERE len (Texte1) <> len (Replace (Texte1 , "*", ""))

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I have an unusual situation. I want to write a SQL Criteria statement that
filters out all textual records that contain a "*". That is, many records
look like this:

Alpha Name
**DO NOT USE!!!**
**DO NOT USE!!!**
Joe's Plumbing

I want to filter out all records that contain an asterisk. How can I do
this? Should I not use the LIKE operator?

Thanks,
Matthew Pfluger