|
Pattern Matching Operators
A pattern matching
operator
is part of a
query,
i.e. an enquiry on a database. In a query, the pattern matching operator compares
columns
of a
table
and assess their matching according to the given pattern.
One of the available pattern matching operators is "like". It can be used with so-called
wildcards
in order to create a very flexible query.
Wildcards for the like operator
-
% - The percentage sign is a wildcard for character strings between 0 and n characters.
select name from your_database..sysusers
where name like "%smith%"
-
_ - the underscore is a wildcad for single characters.
select name from your_database..sysusers
where name like "_mith%"
-
[ ] - the square bracket is a wildcard to set ranges of values.
select name from your_database..sysusers
where name like "[Ss][Mm][Ii][Tt][Hh]"
-
^ - The circumflex is a wildcard to negate pattern recognition.
select name from your_database..sysusers
where name like "[^A-RT-Z]mith%"
The same result can be achieved with this query
select name from your_database..sysusers
where name not like "[A-RT-Z]mith%"
In case one of the above mentioned wildcard characters is part of a searched string, it needs to be
masked.
For this the
"escape"
clause can be used.
This can be achieved by prefixing any character with the masking character and marking it with the "escape" clause as masking character. The following uses a \ (Backslash) as
masking character.
select name from your_database..sysusers
where name like "%_%" escape ""
Besides the "like" operator, the
string function
patindex()
is also available for pattern matching functions.
|
