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
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%'
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'
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%
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
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'
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
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
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
|
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
No comments:
Write comments