![]() ![]() ![]() LIKE with CONCAT In the string supplied the name is matched. SELECT * FROM student WHERE name LIKE 'C%' AND name NOT LIKE '_h%' Searching keyword across multiple columns by using AND, OR Keyword should present in both columns ( by using AND ) SELECT * FROM TABLE_NAME WHERE Column1 Like '%keyword%' AND Column2 LIKE '%keyword%' Keyword should present in any columns ( by using OR ) SELECT * FROM TABLE_NAME WHERE Column1 Like '%keyword%' OR Column2 LIKE '%keyword%' Same way the query can be extended to search across more than two columns. Name of the students having letter 'a' and letter 'e' ( without single quotes ) SELECT * FROM student WHERE name LIKE '%a%' AND name LIKE '%e%' Find all courses from the Section table that start with the character, “C”, but do not have “h”, as the second character. select * from student where name LIKE binary '%A' Try the same query by not using binary inside it. ![]() To match lower only or upper only cases we have to use binary command to make binary matching. Case Sensitive query As we have seen all the above cases are case insensitive. Using NOT with LIKE SELECT * FROM student WHERE name NOT LIKE '%John%' This way we can use LIKE command with many other commands to get desired output. 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. SELECT * FROM account_master WHERE acc_no LIKE '_044' We have used two underscores in our query to tell that first two digits can be any thing and it should end with 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 fields. Use of underscore ( _) as wildcard in string matching The above result have desired word John at the end only. SELECT * FROM student WHERE name LIKE '%John' Here we will allow any character even zero character to the left of the desired word not to the right 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. 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 In this case we will remove the use of % at the left of word John. In other words we want records starting with John only. We 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 after the word John. Please note the use of symbol % in the query. Related Tutorial Pattern match by REGEXP Keyword Search using like Locate Query The above result shows that we will get all the names where John word is present in the record. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |