Use LIKE predicate to search for the "[" character 1

[OceanDesigner]

I have a column of data in which some values may have the text "[none listed]". I am writing a simple query to filter those values. It looks like the following:

qry = "SELECT Name FROM Cust WHERE Name LIKE '%[none%'"

The problem is, the square brackets - [] - are wildcards. Is there an escape character I can use? Any other options?

 

[dbomrrsm]

qry = "SELECT Name FROM Cust WHERE Name LIKE '%none listed%'

the first % means anything can exist before the n and the second % means that anything can exist beyond the d so as long as you dont have and customers with none listed as part of their name you should be ok.

DBomrrsm

 

[OceanDesigner]

I am not alway sure it will say "none listed". Instead it may say "[no name listed]" or "[no name]". I guess I will just have to force the way the data goes in.

 

[dbomrrsm]

the [] are only wildcard I think when used in conjunction with each other so try

qry = "SELECT Name FROM Cust WHERE Name LIKE '%[%'

This should pick up any occurance of [ in any field which I guess shouldnt be in a valid customer name field.

dbomrrsm

 

[dbomrrsm]

sorry should have tested this didnt work
this will

SELECT Name FROM Cust WHERE Name LIKE '%[[%'
ESCAPE '['

good look

dbomrrsm

FROM BOL

Pattern Matching with the ESCAPE Clause
You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in the customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows containing the string 30% anywhere in the comment column, specify a WHERE clause of WHERE comment LIKE '%30!%%' ESCAPE '!'. Unless ESCAPE and the escape character are specified, SQL Server returns any rows with the string 30.

This example shows how to search for the string "50% off when 100 or more copies are purchased" in the notes column of the titles table in the pubs database:

USE pubs
GO
SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased'
ESCAPE '%'
GO