SQL LIKE Operator - SQL TUTORIAL | Software Testing Material

SQL LIKE Operator:

The SQL LIKE operator is used in a WHERE clause to compare a value to similar values using wildcard operators.

There are two wildcard operators used in conjunction with the Like operator.

Percentage sign ( % )

Underscore sign ( _ )

SQL LIKE Syntax:

SELECT column_name(s) FROM table_name WHERE column_name LIKE somevalue;

Example – Like Operator:

SELECT * FROM SCOREBOARD WHERE Playername LIKE 'Sachin';

In the below image, you could find two SELECT queries. First SELECT query returns the complete records of the table SCOREBOARD where as the second SELECT query returns the values only if the Playername is Sachin. We used WHERE clause in the second SELECT query with the LIKE operator.

Example – Like Operator with Wildcard %:

SELECT * FROM SCOREBOARD WHERE Playername LIKE 'D%';

In the below image, you could find two SELECT queries. First SELECT query returns the complete records of the table SCOREBOARD where as the second SELECT query returns the values only if the Playername starts with letter D. We used WHERE clause in the second SELECT query with the LIKE operator and wildcard %.

Example – Like Operator with Wildcard % At The Beginning:

SELECT * FROM SCOREBOARD WHERE Playername LIKE '%h%';

In the below image, you could find two SELECT queries. First SELECT query returns the complete records of the table SCOREBOARD where as the second SELECT query returns the values only if the Playername consists the letter h. We used WHERE clause in the second SELECT query with the LIKE operator and wildcard % before and after the letter h.

Example – Like Operator with Wildcard _:

SELECT * FROM SCOREBOARD WHERE Playername LIKE '_achin';

Example – Like Operator with Wildcard _ Before And After:

SELECT * FROM SCOREBOARD WHERE Playername LIKE '_achi_';

Example – Like Operator with Wildcard combination % and _:

SELECT * FROM SCOREBOARD WHERE Playername LIKE '_a%';

NOT LIKE:

SELECT * FROM Table_Name WHERE SomeColumn_Name NOT LIKE '%Some_Value%';

In the below image, you could find two SELECT queries. First SELECT query returns the complete records of the table SCOREBOARD where as the second SELECT query returns the values from the tables but it eliminates the values which has letter Sac. We used WHERE clause in the second SELECT query with the NOT LIKE operator and the letters Sac with wild card % before and after the letters Sac.