Use * as character, not as wildcard

  • Thread starter Thread starter Matthew Pfluger
  • Start date Start date
M

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
 
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)
 
Awesome! Thanks very much!

Matthew Pfluger

Sylvain Lafontaine said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top