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.