Wildcard and escape characters in SQL Server

Source: Internet
Author: User
Tags character set min

Wildcard characters

Wildcards in SQL are used in place of one or more characters.

SQL wildcard characters are used with the LIKE operator.

Common wildcard characters in SQL Server

%

Matches an arbitrary length character (length can be 0)

If the ' m%n ' matches a string between the characters m and N with any character, the matching example has Man,min,mon,moon, ...

_

Match any single character

If the ' M_n ' matches a string between the characters m and n that contains any one character, the matching example has Man,min,mon, ...

[Character Set fit]

Match any one character in the character set

such as ' [MZ] ' matches the character m or Z, ' [m-z] ' matches any single character between M and Z.

[^]

Match any single character not in parentheses

such as ' [^MZ] ' matches any single character that is not M or Z.

Escape character

Using the escape character, you can tell the DBMS that the wildcard character in the query is no longer a wildcard, but an actual value of the characters.

If "%" is escaped with the escape character, "%" indicates the percent sign itself.

Define escape characters using escape

Look at the following example:

?

1 2 3 4 5 6 7 8 CREATE TABLE tb_testtable (id int not NULL IDENTITY, name NVARCHAR (), PRIMARY KEY (id)); INSERT into tb_testtable (name) VALUES (' Tiana '); INSERT into tb_testtable (name) VALUES (' Tiana5 '); INSERT into tb_testtable (name) VALUES (' tiana5% '); SELECT name from tb_testtable WHERE name like '%/% ' ESCAPE '/'

The example first defines the table tb_testtable, inserts 3 data into it, and finally queries the table.

In a query like '%/% ' escape '/' clause, '/' is defined as an escape character, which tells the DBMS: the second percent semicolon in the string '%/% ' is the actual value, not the wildcard character. Of course, the first percent sign here is still a wildcard character. Therefore, the result of the query here should be: ' tiana5% '.

In SQL Server, if you do not use the escape clause to define an escape character, you can also use the form of ' [%] ' to tell Dnms that the% here is no longer a wildcard character but a percent sign itself.

So the above query can also be written as:

?

1 SELECT name from tb_testtable WHERE name like '%[%] '

The above description also applies to other wildcard characters, which apply to single quotes and double quotes.

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.