search for special character

[thesti]

hi,

how to search a string in a table that contain a special character ?

for example i want to search fo a string "D'Crepes" in a field of a table.
i've tried using the following sql from vb .net

select name from menu where name like '%D'Crepes%'

but since ' is a special character, i got an error. is there anyway to do this, like using \ in mysql?

Thank you,

[ADezii]

@thesti
Try:

SELECT Name From Menu Where InStr([Name],"D'Crepes") > 0;

[nico5038]

An alternative is to use the [ and ] around the special character like:

select name from menu where name like '%D[']Crepes%'
 

These [ and ] can also be used to select a range for one character, e.g. a to k by using "[a-k]"

Nic;o)

[ADezii]

Nico makes an excellent point, just to add to what Nico has said, you can also search for characters outside of the Range by using the '!' within the defined Range. The following Query will return all Last Names that do not begin with the characters a thru k:

SELECT Employees.LastName
FROM Employees
WHERE Employees.LastName Like "[!a-j]" & "*";