|
| |
SQL LIKE query Command |
LIKE sql command is used to retrieve or match records in different combinations to get desired result with wildcard. We will try LIKE sql query command and how to use it in our MySQL tables. But the best way to use LIKE query command is to apply it in a text or varchar field along with wildcard %. Here is our table with all the records.
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 2 |
Max Ruin |
Three |
85 |
| 3 |
Arnold |
Three |
55 |
| 4 |
Krish Star |
Four |
60 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
We will apply the LIKE command here like this to our name field.
SELECT * FROM student WHERE name LIKE '%John%'
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 5 |
John Mike |
Four |
60 |
| 6 |
Alex John |
Four |
55 |
The above result shows that we will get all the names where John word is present
in the record. It can be any where. Please note the use of symbol % in the
query. This will match any number of character even zero character before or
after the word John. So we are getting the names those starts with John also. We
may require the names which only starts with John not inside the name. In other
words we want records starting with John only. In this case we will remove the
use of % at the left of word John. Here is the SQL with LIKE
command.
SELECT * FROM student WHERE name LIKE 'John%'
| id |
name |
class |
mark |
| 1 |
John Deo |
Four |
75 |
| 5 |
John Mike |
Four |
60 |
We can see the result above list out all the names starting with name John. To
display the records which does not have specific word John as the beginning we
have change our LIKE sql command a little by changing the % position to the end
of the word. Here we will allow any character even zero character to the left of
the desired word not to the right
SELECT * FROM student WHERE name LIKE '%John'
| id |
name |
class |
mark |
| 6 |
Alex John |
Four |
55 |
The above result have desired word John at the end only.
Use of underscore ( _) as wildcard in string matching
We can use underscore as wildcard for one character space and use them along with Like statement and apply to fields. For example we want to collect all the account numbers ending with 044 in a five digit account number field. Here is the query for this.
SELECT * FROM account_master WHERE acc_no LIKE '__044'
We have used to underscores in our query to tell that first two digits can be any thing and it should end with 044.
Underscores as wildcard can be used at any location but one can replace one character only. We can use more than one underscore also inside our query.
This way we can
use LIKE command with many other commands to get desired output.
Read how LIKE command is used in MSSQL database
| |
|
| HOME |
| SQL Tutorial List |
| SQL (Home) |
| SQL Commands |
|
|
|
|
|
| Subscribe |
|
Submit your email address and receive
article and product notifications. Your email is safe with us.
|
|
|
|
