SQL Server LIKE

The LIKE operator is used in a where clause to find specified pattern of character from string or column.LIKE operator uses two type of wild card character % or _.

 % - find zero, one, or multiple characters pattern.
 _ -  find represents a single character.

Here is type , how you can use the LIKE operator.


1) Name LIKE 'a%'
Finds any values that start with a.    

2)Name  LIKE '%a%'
Finds any values that have a in any position.  

3)Name LIKE '_ab%'
Finds any values that have ab in the second and third positions.

4)Name LIKE   'a_%_%'
Finds any values that start with a and are at least 3 characters in length.

5)Name LIKE '%a'
Finds any values that end with a.

6)Name LIKE '_a%b'
Finds any values that have a in the second position and end with a b.

Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student

(

         id INT NOT NULL,

         name VARCHAR (500) NOT NULL,

         age INT NOT NULL,

         adress VARCHAR (500) NOT NULL,

         date_of_birth DATETIME NOT NULL,

         height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',15,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',18,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',20,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',22,'44,student street','11/08/2018',16

Example 1

SELECT name

         FROM student

                              WHERE name LIKE 'J%'

name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 2

 

SELECT name

        FROM student

                          WHERE name LIKE '%h%'

 name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 3

SELECT name

            FROM student

                          WHERE name LIKE '%n8'

name

John8

Example 4

 

SELECT name

FROM student

WHERE name LIKE 'j_%_%'

name

John1

John10

John11

John2

John3

John4

John5

John6

John7

John8

John9

Example 5

 

SELECT name

         FROM student

                        WHERE name LIKE '%7'

name

John7

Example 6

SELECT name

FROM student

WHERE name LIKE '_oh%6'

name

John7