SQL LIKE & Wildcards

The LIKE operator is used to search for a specified pattern in a column



SQL LIKE SYNTAX

SELECT column_name(s)

FROM table_name
WHERE column_name LIKE pattern

The "custmast" table:

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example : 1

Now we want to select the Person living in a city that starts with "S" from the table above.

SELECT * FROM custmast
WHERE City LIKE 'S%'

Output : 1

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example : 2

Next, we want to select the persons living in a city that ends with an "S" from the "Persons" table.

SELECT * FROM custmast
WHERE City LIKE '%S'

Output : 2

custcode	custname	city	qty	rate
5	Kodee	Sandnes	60	80

Example : 3

Next, we want to select the persons living in a city that contains the pattern "put" from the "custmast" table.

SELECT * FROM custmast
WHERE City LIKE '%put%' 

Output : 3

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
5	Kodee	Srivilliputtur	60	30

Example : 4 

SELECT * FROM custmast
WHERE City NOT LIKE '%put%

Output : 4

custcode	custname	city	qty	rate
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30

                                                      

                                                       SQL WILDCARD

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

Already to explain '%' wildcard .. So Next to Explain " _ " 

                                          A substitute for exactly one character "_"


Now we want to select the persons with a custname that starts with any character, followed by "va" from the "custmast" table.

Example : 5 
SELECT * FROM custmast
WHERE Custname LIKE '_va'

Output : 5

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70

Example : 6  
Next, we want to select the persons with a City that starts with "S", followed by any character, followed by "vi", followed by any character, followed by "ur" from the "custmast" table.

SELECT * FROM Persons
WHERE city LIKE 'S_vi_ur'

Output : 6

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
5	Kodee	Srivilliputtur	80	30
5	Kodee	Srivilliputtur	60	30

    

                                                Any single character in charlist  [charlist] 


Example : 7 
Next, we want to select the persons with a custname that do start with "s" or "b" or "k" from the "custmast" table.

SELECT * FROM Persons
WHERE custname LIKE '[sbk]%'

Output : 7

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200

Example : 8 
 we want to select the persons with a custname that do End with "s" or "b" or "k" from the "custmast" table.

SELECT * FROM Persons
WHERE custname LIKE '%[ey]' 

Output : 8

custcode	custname	city	qty	rate
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example : 9
SELECT * FROM Persons
WHERE custname LIKE '%[!ey]'

Output : 9

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200

* check custmast table

 Share This Post