Wildcard
The wildcard character in SQL is used to replace one or more characters.
Use the SQL wildcard with the LIKE operator.
Common wildcards in SQL Server
%
Match any length (the length can be 0) characters
For example,'m % n' matches any character string between characters m and n. The matching examples include man, min, mon, moon ,...
_
Match any single character
For example,'m _ n' matches strings with any character between characters m and n. The matching examples include man, min, mon ,...
[Character Set combination]
Match any character in the character set
For example, '[mz]' matches the character m or z, '[m-z]' matches any single character between m and z.
[^]
Match any single character not in parentheses
For example, '[^ mz]' matches any single character that is not m or z.
Escape characters
Using escape characters, you can tell DBMS that the wildcard in the query is no longer a wildcard, but the actual value of the character.
For example, after "%" is escaped using the escape character, "%" indicates the percentage.
Use ESCAPE to define ESCAPE characters
See the following example:
?
1 2 3 4 5 6 7 8 |
Create table tb_TestTable (id int not null identity, name NVARCHAR (64), 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 '/' |
In this example, we first define the table tb_TestTable, insert three data records into the table, and then query the table.
In the LIKE '%/%' ESCAPE '/' clause in the query, '/' is defined as an ESCAPE character, which tells DBMS: the second percentage in the string '%/%' is the actual value, not the wildcard character. Of course, the first percent is still a wildcard. Therefore, the query result here should be: 'tiana5% '.
In SQL SERVER, if you do not use the ESCAPE clause to define ESCAPE characters, you can also use '[%]' to tell DNMS: Here, % is no longer a wildcard but a percent.
Therefore, the preceding query can also be written as follows:
?
1 |
SELECT name FROM tb_TestTable WHERE name LIKE '% [%]' |
The above description also applies to other wildcard characters, as well as single quotation marks and double quotation marks.