SQL server:sql like wildcard special usage: Escape

Source: Internet
Author: User


%: 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.