Wildcard (%, _, [],[^]) in SQL Server

Wildcard characters are used in the where clause to search a string with some patterns. for instance, checking a name that starts with ‘Mic’ and rest of the characters may be anything. In this scenario we can use wildcard characters to represent patterns in the search string.

There are four type of wildcard character available in SQL Server. We have to use LIKE or PATINDEX keyword to use pattern matching with wildcard characters.

%  is used to represent anything before, after or whole string.

Example:

use AdventureWorks

select * from Production.Product where Name like '%'  -- Anything
select * from Production.Product where Name like 'Bl%' -- Starts with Bl and rest anything
select * from Production.Product where Name like '%Bl%' -- anything before Bl and after Bl

[] is used to search single character within a range (A-Z or 0-9) or a single character in the pattern matching.

use AdventureWorks

--searching a string  'SA-M' with any number range from 0 to 9 and anything after the number
select * from Production.Product where ProductNumber like 'SA-M[0-9]%'

/*Searching a sting 'LI-' and next character must be 1 and followed by any numeric 0 to 9 and must followed by 0 and anything
select * from Production.Product where ProductNumber like 'LI-[1][0-9][0]%'

[^] is used to search for a string without the character given in the square bracket after ^symbol and in specified position.

use AdventureWorks

--searching for a string start with 'A' and the second character is not 'B' and anything
select * from Person.CountryRegion where Name like 'A[^b]%'

_ (underscore) is used to match a single character pattern match in a string.

use AdventureWorks

--searching for a string start with any character and the second character is  'm' and rest anything
select * from Person.CountryRegion where Name like '_m%'

Learn New functions in SQL Server 2012. Thanks for reading.

About these ads