SQL LIKE OPERATOR

The LIKE operator is used to compare the values in records by values pattern and return the result set. There are two wildcards used with LIKE operator as following.

  • % (Percent Sign)
  • _ (Underscore)

    You must have to use WHERE condition to use this LIKE operator in your sql statement. The
    % (Percent sign) represent one, zero or multiple characters while _ (Underscore) represent single character or number only.


You can use N number time LIKE operators in your sql statement by using AND | OR operators.

Let’s understand each with different syntax and examples.

MS SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE PATTERN;

Other Syntax’s:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘X%’;

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘%X%’;

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘X%’;

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘_X_’;

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘_X’;

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN LIKE ‘X_’;

X = This will be your value in either text or number.


Now, we will use few sample sql statement with both operators in following and see what result it display.


Examples:

Original Data in table called “Employee”

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Gil Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Martyn Mondry Male 02-06-1991


SELECT * FROM Employee
WHERE FirstName LIKE 'jo%';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980


SELECT * FROM Employee
WHERE FirstName LIKE '%il%' OR Lastname LIKE '%il%';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Gil Parkar Female 08-09-1990


SELECT * FROM Employee
WHERE FirstName LIKE '%an';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
5 EMP_1005 Martyn Mondry Male 02-06-1991


SELECT * FROM Employee
WHERE FirstName LIKE '_osh';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
2 EMP_1002 Josh Wills Male 09-07-1980


SELECT * FROM Employee
WHERE FirstName LIKE 'Jo__';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980


SELECT * FROM Employee
WHERE FirstName LIKE '_i_';

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date
3 EMP_1003 Gil Parkar Female 08-09-1990