The SQL LIKE Operator

The LIKE operator is used to search for a specific content in a column of the table. The LIKE operator is used with WHERE clause. To search for a particular pattern , some wildcard characters are used with the LIKE operator.

WildCard characters

% - is used with LIKE operator to search with zero , one or more characters.

_ - is used with LIKE operator to search with single character.

Syntax

SELECT TOP column_name1,column_name2
FROM table_name
WHERE LIKE pattern

Both the percentage and underscore sign can also be used in combination.

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

The below query will find the record where the value in the Name column will start with 'g' and end with anything.

SELECT * FROM [tbl_Record]
WHERE Name like 'g%'

Output

ID	Name	State	JoinDate	Experience
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

The below query will find the record where there is a single character at 2nd and 4th position.

SELECT * FROM [tbl_Record]
WHERE State like 'D_l_i'

Output

ID	Name	State	JoinDate	Experience
3	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3

The below query will find record with any characters at first two positions but ending with 'har'

SELECT * from [tbl_Record] WHERE
State like '__har'

Output

ID	Name	State	JoinDate	Experience
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4

The below query will find the records whose value in the name column ends with 'h'.

SELECT * FROM [tbl_Record] WHERE
Name like '%h'

Output

ID	Name	State	JoinDate	Experience
2	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

The below query will search for the records whose value in the name column is 'a' at the second place and after that any number of characters.

SELECT * from [tbl_Record]
WHERE Name like '_a%'

Output

ID	Name	State	JoinDate	Experience
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
3	Tarun sharma	Maharashtra	2017-08-11 10:15:25.000	5

The below query will search the record where name has value of 'jit' at any position.

SELECT * FROM tbl_Record
WHERE name like '%jit%'

Output

ID	Name	State	JoinDate	Experience
2	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

The below query will search the record in the table where value of the column field starts with 't' and ends with 'a'.

SELECT * FROM tbl_Record
WHERE Name like 't%a'

Output

ID	Name	State	JoinDate	Experience
3	Tarun sharma	Maharashtra	2017-08-11 10:15:25.000	5