Friday, 30 January 2009

Searching for a wildcard character in SQL

There are occasions when you need to search for the wildcard character within SQL.

The wildcard characters are:

  • % : Any string of zero or more characters.

  • _ : Any single character.

  • [ ] : Any single character within the specified range ([a-f]) or set ([abcdef]).

  • [^] : Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

To search for any of these characters you need to use the ESCAPE clause.

To search for a text value that contains 10-15% you can use:

SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'

No comments: