SQL Server: SQL like wildcard special usage: Escape

 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 '\'