SQL LIKE query to use wildcards to partially match strings with combination of AND OR NOT

Download the tutorial , SQL dump and solution to the questions in SQL format.

We need to always use exact matching against any string, we can also use partial matching by using wildcards along with LIKE query in MySQL.
Matching at the staring of the string. The name John should be there in staring of the string and rest of the string can be anything.
SELECT * FROM student WHERE name LIKE ‘John%’
Matching at the ending of the string. The name John should be there in ending of the string and first of the string can be anything.
SELECT * FROM student WHERE name LIKE ‘%John’
Matching the string at any part of the string. So the search string can be at the starting or ending or in between the string.
SELECT * FROM student WHERE name LIKE ‘%John%’
All these searches are case insensitive. To make it case sensitive we have to use Binary search. Here is the query.
SELECT * FROM student WHERE name LIKE binary ‘%no%’
By using underscore we can match against any single character. We can use more than one underscore to match more chars but we have to specific with the number of chars. 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 can use OR and AND combinations along with our LIKE query.
We can collect all records having alex or deo inside it.
SELECT * FROM student WHERE name LIKE ‘%alex%’ OR name like ‘%deo%’
We can use AND condition also
SELECT * FROM student WHERE name LIKE ‘%alex%’ AND name like ‘%Joh%’
We can use OR here
SELECT * FROM student WHERE name LIKE ‘%alex%’ OR name like ‘%Joh%’
Let us combine both wildcards % and _.
Let us find out the record having ro any where in name column and mark is in nineties

SELECT * FROM student WHERE name LIKE ‘%ro%’ AND name like ‘9_’

[everwelwisher]

Thanks you very much sir, God Bless you... Very useful and very great way to explain with instant examples.. So nice of you sir