Wildcard and escape characters in SQL Server

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.