| | |
Pattern Matching in a query by using LIKE command |
We can use LIKE command inside a query with SELECT & WHERE combination to get records with some string matching. This is of great use while we are developing a search query or any other type of application where we expect to get a matching pattern to the query.
Download the student table with structure and data in csv format for your study and testing. You can use the same display script used there.
Inside a Query LIKE command is used in many combinations. Here we will see how the command is used for queries designed for MSSQL table. There is no great difference here but you can read how the LIKE command is used for MySQL in our sql section.
Here is the query we designed to collect all the records from student table starting with letter J
rs1.open " select * from student WHERE name LIKE 'J%' " , conn
Now let us find out the records ending with a single char e
rs1.open " select * from student WHERE name LIKE '%e' " , conn
Now let us find out the records having two chars inside their name any where.
rs1.open " select * from student WHERE name LIKE '%ff%' " , conn
This way we can use wildcard to get the required pattern combinations in our query. Now let us try to match underscore ( _ ) as a replacement for a single position.
rs1.open " select * from student where mark like '5_' " , conn
The above query will return all the records with mark is between 50 and 59 ( both ends inclusive ) . Here we have specified that the first digit should be 5 and second digit can be any thing. We can sue underscore for more than one place also.
| |
|
|
|
