Translate to Tamil

Amazon Best Offer

AliExpress Offers

Online Income

Wikipedia

Search results

Friday, 10 December 2010

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


Services

gads

Online Earning

© 2014