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%'

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'

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%'

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%

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'

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'

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]%'

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]'

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

Example : 8


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

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

* check custmast table