SQL server:sql like wildcard special usage: Escape

%: matches 0 or more arbitrary characters; _: matches any single character; []: Matches a range; [^]: excludes a range;-: hyphen

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 appearing in strings: '% ', ' [', ' [] ', ' _ ' can be included with ' [] ', so that they are treated as ordinary characters in the pattern.

1. Use like ' [[] ' to match Special characters ' ['

Select 1 where ' [ABCDE ' like ' [[]% '

2. Match Special characters with like '] '

Select 1 where ']abcde ' like ']% '

3. Use like ' [[]] ' to match Special characters ' [] '

Select 1 where ' []abcde ' like ' [[]]%% '

4. Use like ' [_] ' to match Special characters ' _ '

Select 1 where ' _abcde ' like ' [_]% '

5. Match Special characters '% ' with like ' [%] '

Select 1 where ' abc%de ' like ' abc[%]de '

For other special characters: ' ^ ', '-', ' ' because they are used in ' [] ' themselves, they need to be escaped in a different way, so the escape clause in like is introduced, and it is also 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 in where '%abcde ' like '!%abcde ' escape '! ' Select 1 where '%abcde ' #%abcde ' escape ' # ' select 1 where '%abcde ' like ' @%abcde ' escape ' @ ' select 1 where ' [ABCDE ' Like '! [ABCDE ' Escape '! ' Select 1 where ']abcde ' like '!] ABCDE ' escape '! '

See the law, is to use escape behind the character character to do escape characters. The character following the escape is equivalent to the escape character in the C language string ' \ '.

Finally, look at a more complex match, and note that "-" This is also a special character that needs to be escaped.

Select 1 where ' [^a-z]abcde ' like ' \[\^a\-z\]% ' escape ' \ '

PostScript: Used regular expression of friends should know that in the regular expression can be used to match 0 or more times, [\u4e00-\u9fa5] This can match Chinese, but here I did not find a similar function, so matching some complex statement is more headache. If I want to match shape such as: Number of sheets * unit price, number of 200+ sheets * unit Price, number of sheets, but mismatch shape such as: Number of sheets 5* unit price, number of sheets 6767, 55 sheet number UU data is difficult to achieve.

The following is to match the word "spinning dream", but the two words can only be connected with the "+-*/" four characters or exist alone. If the "Spinning dream" here is any Chinese characters, there is no way to deal with it.

CREATE TABLE #tbl (n int,v nvarchar) insert INTO #tbl     Select 1, ' 1+ Dream 1+2 '     Union select 2, ' 1+ spinning Dream +1 '     union Select 3, ' Spinning Dream '     Union select 4, ' Spinning Dream +1 ' select * from #tbl where (v like '%[+\-*/] spinning dream [+\-*/]% ' ESCAPE ' \ ')    or (v like ' spinning Dream [+\-*/]% ' escape ' \ ')     or (v like '%[+\-*/] spinning dream ' ESCAPE ')    or (v = ' spinning dream ')--select * from #tbldrop table #tbl

SQL server:sql like wildcard special usage: Escape