Select * from student where name LIKE binary '%A' To match lower only or upper only cases we have to use binary command to make binary matching. This way we can use LIKE command with many other commands to get desired output.Īs we have seen all the above cases are case insensitive. Using NOT with LIKE SELECT * FROM student WHERE name NOT LIKE '%John%'

We can use more than one underscore also inside our query. Underscores as wildcard can be used at any location but one can replace one character only. We have used two underscores in our query to tell that first two digits can be any thing and it should end with 044. SELECT * FROM account_master WHERE acc_no LIKE '_044' For example we want to collect all the account numbers ending with 044 in a five digit account number field. We can use underscore as wildcard for one character space and use them along with LIKE statement and apply to table columns. Use of underscore ( _) as wildcard in string matching Matches string starting with j and ending with n Here is a summary of string matching using % along with LIKE query

SELECT * FROM student WHERE name LIKE 'A%n' Matching string in name column starting with A and ending with n. The above result have desired word John at the end only. SELECT * FROM student WHERE name LIKE '%John' id Here we will allow any character even zero character to the left of Have change our LIKE sql command a little by changing the % position to the end Toĭisplay the records which does not have specific word John as the beginning we We can see the result above list out all the names starting with name John. Read how Regular expression is used to Pattern matching SELECT * FROM student WHERE name LIKE 'John%' id Words we want records starting with John only. May require the names which only starts with John not inside the name. So we are getting the names those starts with John also. This will match any number of character even zero character before orĪfter the word John. The above result shows that we will get all the names where John word is present Related Tutorial Pattern match by REGEXP Keyword Search using like Locate Query

Mysql like with underscore