The SQL LIKE clause is used to match a value to a similar values using wildcard operators. It is used with Where Clause.

There are two type of wildcards used in conjunction with the LIKE operator.

• The percent sign (%) that is  used to represent zero , one, or multiple characters.
• The underscore (_) that is used to represent a single number or character.

Following are the list of Wild Card operators in SQL.

%  :   It represents zero or more characters.

[ ]  :  It represents a single character within the brackets.

_    :  It represents a single character.

–    :  It represents a range of characters.

^    :  It is used with bracket [] , and represents any character that is not in the bracket.

Lets look at an example of LIKE clause with wild card operators.

Like  ‘T%’ finds the CusotmersId that start with ‘T’

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE 'T%'

Like  ‘T%’  finds the CusotmersId that end with ‘T’

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '%T'

Like  ‘__T%’ finds the cusotmersId that have any character in first and second position and  ‘T’ in third position

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '__T%'

Like  ‘W__T_%’ finds the CusotmersId having 5 digits , start with ‘W’ and any character in second and third position and ‘T ‘ in the fourth position 

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE 'W__T_%'

 Like  ‘%EL%’ finds CusotmersId that have ‘EL’ in any position

Select OrderId ,CustomerId ,OrderDate from Orders
Where CustomerID LIKE '%EL%'

 Like  ‘AN%’  –  It finds employee LastName that end with ‘AN’

Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '%AN'

 Like  ‘_[UE]’  –  It finds employee LastName that start with any character and second character can be either ‘E’ or ‘U’ only

Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '_[ue]%'

Like  ‘_[^UE]’  finds employee LastName that start with any character and character in second position can not be ‘E’ and ‘U’ 

Select EmployeeID , LastName ,FirstName from Employees
Where LastName LIKE '_[^ue]%'

Like  ‘__[A-L]’ finds employee LastName have any character in first and second position and  third position character can be any character between A to L

Select EmployeeID, LastName, FirstName from Employees
Where LastName LIKE '__[A-L]%'

Like  ‘[1-5,A-C]’ finds employee Address that start with any character between 1-5  or A-C 

Select EmployeeID ,Address from Employees
Where Address LIKE '[1-5,a-c]%'

 

 207 total views,  2 views today