T-SQL Escape Select .... special characters in like (percent semicolon)

Source: Internet
Author: User

It is well known that the LIKE operator in T-SQL uses a% symbol to denote wildcards. Most of the time, you may need to query for data that contains a percentage, such as Querying the field coupon contains 5% of the data. So how do you use a like search string that already has a percent sign (%) symbol?

As you can find from MSDN, the wildcard characters in like include:

Wildcard characters

Description

Example

%

An arbitrary string containing 0 or more characters.

The where title like '%computer% ' will find all titles that contain the word "computer" anywhere in the title.

_ (Underline)

Any single character.

WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN.

[ ]

Any single character in the specified range ([a-f]) or collection ([abcdef]).

WHERE au_lname like ' [C-p]arsen ' will find the last name of the author that ends with Arsen and starts with any single character between C and P, such as Carsen, Larsen, Karsen, and so on. In a range search, the range contains characters that may vary depending on the collation of the collation.

[^]

Any single character that does not belong to the specified range ([a-f]) or collection ([abcdef]).

WHERE au_lname like ' de[^l]% ' will look for the last names of all authors that begin with de and whose letters are not followed by L.

What about using wildcards as text?

    1. Method 1 uses parentheses, such as 75%
WHERE mycol like '%75[%]% '

The following table shows several examples of using the LIKE keyword and [] wildcard characters.

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

Method 2 using the ESCAPE clause
SELECT *
From table
WHERE mycol like '%75!%% '
ESCAPE '! '

T-SQL Escape Select .... special characters in like (percent semicolon)

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.