SQL Server: SQL like wildcard special usage: Escape
%: Match zero or multiple arbitrary characters; _: match any single character; []: match a range; [^]: exclude a range
symbol |
meaning |
like '5 [%] ' |
5% |
like '[_] n' |
_ n |
like '[A-CdF]' |
A, B, C, D, or F |
like '[-ACDF]' |
-, A, C, D, or F |
like '[[]' |
[ |
like ']' |
] |
like 'abc [_] d % ' |
abc_d and abc_de |
like 'abc [DEF] ' |
ABCD, abce, and abcf |
like '[^ 1-9]' |
0 |
like '[^ 1-9b-z]' |
0, a |
For special characters in a string: '%', '[', '[]', '_', you can use '[]' to include them, in this way, pattern is treated as normal characters.
1. Use like '[[]' to match the special character '['
Select 1 Where '[ABCDE 'like' [[] %'
2. Use like ']' to match the special character ']'
Select 1 Where '] ABCDE 'like'] %'
3. Use like '[[]' to match the special character '[]'.
Select 1 Where '[] ABCDE' like '[[] %'
4. Use like '[_]' to match the special character '_'
Select 1 Where '_ ABCDE' like '[_] %'
5. Use like '[%]' to match the special character '%'
Select 1 where 'abc % de' like 'abc [%] de'
For other special characters: '^', '-', ']', because they are used in '[]', they must be escaped in another way, the like clause is introduced, and it is worth noting that escape can escape all special characters.
Select 1 Where '^ ABCDE 'like '! ^ ABCDE 'escape '! 'Select 1 where'-ABCDE 'like '! -ABCDE 'escape '! 'Select 1 where'] ABCDE 'like'!] ABCDE 'escape '! 'Select 1 where' % ABCDE 'like' \ % ABCDE 'escape '\ 'select 1 where' % ABCDE 'like '! % ABCDE 'escape '! 'Select 1 where' % ABCDE 'like' # % ABCDE 'escape '# 'select 1 where' % ABCDE 'like' @ % ABCDE 'escape' @ 'select 1 where '[ ABCDE 'like '! [ABCDE 'escape '! 'Select 1 where'] ABCDE 'like'!] ABCDE 'escape '! '
As you can see, we can use the characters followed by escape as escape characters. The character after escape is equivalent to the escape character '\' in the C-language string '\'.
Finally, let's look at a more complex match.
Select 1 Where '[^ A-Z] ABCDE 'like' \ [\ ^ A \-Z \] % 'escape '\'