![]() We can put this exact requirement in two ways, first by mentioning all three characters as an array and second by mentioning the range of characters from ‘a’ to ‘c’.Ĭode: select * from emp where E_Name REGEXP '' Here we can see how to search for a set of characters and a range of characters.įor example, let’s assume we need to identify the rows from the employee table with any of the characters’ a,’ b’, and ‘c’ in the E_Name column. So far, we have discussed searching for a single character, word, or a portion of a word. And as the output picked two entries from the table, which have character ‘a’ at the final character of the location, California and Alabama. ![]() The query specifies to select entries from the employee table for which the location field ends with the character ‘a’. Here we see how to search for an ending character/ word/ part of the word.Ĭode: select * from emp where Location REGEXP 'a$' Also, since the BINARY function is mentioned in the query, it will check for case sensitivity too. And from the sample table, we get two rows satisfying the condition, which are New York and New Jersey. The query says to select the entries for which the location field starts with the word ‘New’. Here we can search a part of a word as well.Ĭode: select * from emp where Location REGEXP BINARY '^New' Here we got the details of employees with the character ‘E’ in upper case at the beginning. Here let’s make it upper case and try to retrieve the output.Ĭode: select * from emp where E_Name REGEXP BINARY '^E' Since we specified the lowercase character ‘a’ in the query, no results could be retrieved. Here we make it more specific by making it case-sensitive.Ĭode: select * from emp where E_Name REGEXP BINARY '^a' So far, the characters or strings considered as the pattern were not case-sensitive. Here, we used the operator ‘^’ along with the pattern to be searched and thus got the details of employees whose name starts with the character ‘a’. Here we see how to select the details of an employee whose name starts with a specific character.Ĭode: select * from emp where E_Name REGEXP '^a' The specified character could be anywhere in the word. Here we just mentioned selecting data with a character. So we wrote the query for selecting details with a string in it. And as we look at the output, we can understand that out of 5 employees, 3 have the character ‘a in their names: Alan, Carl, and Dave. To analyze the query, we have described it to select all details from table ’emp’ with the character’ a’ in their E_Name field. To master more advanced optimisation techniques, consider our SQL optimisation training for teams.Here we need to get the details of employees with the character ‘a’ in their names.Ĭode: select * from emp where E_Name REGEXP 'a' But running RLIKE and LIKE against a small set of rows is much faster than running a regular expression on all the rows in a table. So you’d probably think that the second version takes more time. I understand that this is counter-intuitive: looking at the optimised version of the query, you see that it has to run both a regular expression and a LIKE comparison. Scanning a whole table may take a long time if the table is big. Finding a set of rows by index normally takes much less than a second. If it wasn’t ordered, you’d have to read all the names one by one, and I don’t know how much it would take – probably months, or years.Ī computer is obviously faster than you and me, but the principle is the same. If you had to look Tom Baker on a British phone book, you’d find it in seconds: because of the alphabetical order, you could just skip all names except for a small number of them. Why is this an optimisation?Īn index is an ordered data structure, just like a phone book. If so, the rows column should show that the number of rows examined by the second query is much lower. Check that an index is usable ( possible_keys column) and is actually used ( key column, type=range). It will check the regular expression against the rows that satisfy the LIKE condition.Įven in the worst case (ie, no index used) this won’t be sensibly slower than using the LIKE condition alone. ![]() MariaDB will check the LIKE expression over an index.WHERE code LIKE 'AB%' AND code RLIKE '^AB]+' Unfortunately, this won’t check if AB is followed by digits.īut what about this condition? SELECT id, code ![]() This condition should use an index: WHERE code LIKE 'AB%' That is a pity, because we are looking for a precise substring at the beginning of a string, and this search could theoretically use an index ( range search). LIKE to the rescueĪs mentioned at the beginning of this article, a regexp can never make use of an index. The whole regexp means: “a string that starts with AB, immediately followed by at least one digit”. ![]() AB is just a sequence of characters we’re looking for.Just in case you don’t understand regexp syntax: Consider the following regexp: SELECT id, code FROM product WHERE code RLIKE '^AB]+' ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |