Reputation: 6795
What is the complete list of all special characters for a SQL (I'm interested in SQL Server but other's would be good too) LIKE clause?
E.g.
SELECT Name FROM Person WHERE Name LIKE '%Jon%'
%
- Any string of zero or more characters._
- Any single character%
- Any string of zero or more characters._
- Any single characterSybase
%
- Any string of zero or more characters._
- Any single character
Reference Guide here [PDF]
%
- Any string of zero or more characters._
- Any single characterPostgreSQL also has the SIMILAR TO
operator which adds the following:
[specifier]
[^specifier]
|
- either of two alternatives*
- repetition of the previous item zero or more times.+
- repetition of the previous item one or more times.()
- group items togetherThe idea is to make this a community Wiki that can become a "One stop shop" for this.
Upvotes: 147
Views: 376501
Reputation: 17384
Interesting I just ran a test using LinqPad with SQL Server which should be just running Linq to SQL underneath and it generates the following SQL statement.
Records .Where(r => r.Name.Contains("lkjwer--_~[]"))
-- Region Parameters
DECLARE @p0 VarChar(1000) = '%lkjwer--~_~~~[]%'
-- EndRegion
SELECT [t0].[ID], [t0].[Name]
FROM [RECORDS] AS [t0]
WHERE [t0].[Name] LIKE @p0 ESCAPE '~'
So I haven't tested it yet but it looks like potentially the ESCAPE '~'
keyword may allow for automatic escaping of a string for use within a like expression.
Upvotes: 0
Reputation: 16661
For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx :
% Any string of zero or more characters.
WHERE title LIKE '%computer%'
finds all book titles with the word 'computer' anywhere in the book title.
_ Any single character.
WHERE au_fname LIKE '_ean'
finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
WHERE au_lname LIKE '[C-P]arsen'
finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
WHERE au_lname LIKE 'de[^l]%'
all author last names starting with de and where the following letter is not l.
Upvotes: 27
Reputation:
You should add that you have to add an extra ' to escape an exising ' in SQL Server:
smith's -> smith''s
Upvotes: 5
Reputation: 536765
It is disappointing that many databases do not stick to the standard rules and add extra characters, or incorrectly enable ESCAPE with a default value of ‘\’ when it is missing. Like we don't already have enough trouble with ‘\’!
It's impossible to write DBMS-independent code here, because you don't know what characters you're going to have to escape, and the standard says you can't escape things that don't need to be escaped. (See section 8.5/General Rules/3.a.ii.)
Thank you SQL! gnnn
Upvotes: 6
Reputation: 8185
Sybase :
% : Matches any string of zero or more characters.
_ : Matches a single character.
[specifier] : Brackets enclose ranges or sets, such as [a-f]
or [abcdef].Specifier can take two forms:
rangespec1-rangespec2:
rangespec1 indicates the start of a range of characters.
- is a special character, indicating a range.
rangespec2 indicates the end of a range of characters.
set:
can be composed of any discrete set of values, in any
order, such as [a2bR].The range [a-f], and the
sets [abcdef] and [fcbdae] return the same
set of values.
Specifiers are case-sensitive.
[^specifier] : A caret (^) preceding a specifier indicates
non-inclusion. [^a-f] means "not in the range
a-f"; [^a2bR] means "not a, 2, b, or R."
Upvotes: 1