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 cusotmers Id having a 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 Cusotmers Id 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]%'
1,899 total views, 1 views today