SQL LIKE Operator in Queries

Spread the love

SQL LIKE

SQL LIKE operators are used in conjunction with WHERE clause to find patterns in the strings.

Two wild card characters are used with LIKE operators-
%’ is used to represent  zero, one, or multiple character
_’ is used when you want to represent only one character

Syntax:

WHERE col_name LIKE '%ON'

WHERE Clause in above statement will match with any string which ends with ‘ON’.

WHERE col_name LIKE 'SO_'

WHERE clause in the above statEment will match with any string which is 3 character long and starts with ‘SO’ .

EXAMPLE:

LIKE ExpressionExplanation
LIKE ‘%ke’Match with a string that ends with ‘KE” (example: cake, bake, etc.)
LIKE ‘b_t’Match with string that is 3 character long and starts with ‘B’ and ends with ‘T’ (example: bot, bit, etc.)
LIKE ‘_oo_’Match with String that is 4 character long and contains ‘oo’ in the middle. (example: boom, doom, etc.)
LIKE ‘%oo%’Match with string that has double o (‘oo’) in it. (example: maroon, Cameroon, etc.)
LIKE ‘_um’Match with string that is 3 character long and ends with ‘um’ (example: gum, rum, etc.)

Example With Demo Table:

For demonstration we are going to use ‘EMPLOYEE‘ table.
Note: keep in mind SQL language is not case sensitive. Learn basic from here

EXAMPLE 1 :

SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE 'A%' ;

EXAMPLE 2 :

SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%N' ;

EXAMPLE 3 :

SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%an%' ;

EXAMPLE 4:

SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '_or_' ;

Thanks For Reading!

SOURCE : Microsoft T-SQL