For example, we may want to list any product descriptions where the word Drive or Drives is used. The '?' metacharacter is particularly useful when we need to allow for plural words. Here we see that our Dell XPS 400 is no longer listed because it has only 3 digits. | 3 | WildTech 250Gb 1700 | SATA Disk Drive | | 2 | EasyTech Mouse 7632 | Cordless Mouse | This is achieved using a set of repetition metacharacters: Metacharacter Description * Any number of matches + One or more matches ' In addition to allowing searches on single instances, regular expressions can also be written to look for repetition in text. Regular Expression Repetition Metacharacters | 18 | One&One VoIP Headset | Stereo Headset | Similarly, to extract the 'One&One' product we would use the class: mysql> SELECT * FROM product WHERE prod_name REGEXP 'One]One' | 19 | One2One USB Hub | 4 Port USB Hub | In order to retrieve the One2One product we would use the character class: mysql> SELECT * FROM product WHERE prod_name REGEXP 'One]One' Suppose in our product database we have two products with similar names, the 'One&One VoIP Headset' and the One2One USB Hub. Equivalent to ] Any printable character ] Characters that are neither control characters, nor alphanumeric (i.e punctuation characters) ] Any whitespace character (tab, new line, form feed, space etc) ] Upper case letters. Equivalent to ] Any character with the exception of space ] Lower case letters. Equivalent to and ] ASCII Control Character ] Numeric. Equivalent to, and ] Alpha - any letter. Some examples of these special classes in action follow the table: Class Keyword Description of Matches ] Alphanumeric - any number or letter. This involves the use of some special class definitions outlines in the following table. For example we might need to specify that a character must be a letter, a number or alphanumeric. If instead we escape the brackets with the \ escape sequence we get the result we want: mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model \' Īnother useful regular expression trick is to match characters by type or class. Clearly, what we wanted was the actual text. The cause of the problem is that the regular expression has been interpreted as a search for any entries that read 'CD-RW Model' followed by either a 7, 5, 4 or 3. If we were to search for this without regard to the fact that the prod_name value contains regular expression special characters we will not get what we wanted: mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model ' For example, suppose we have a row in our product table which reads as follows: +-+-+-+ In SQL, escaping involves preceding any characters that may be misinterpreted as a regular expression special character with double back slashes (\). To address this issue, a concept known as escaping is used. Obviously if you are are looking for text that looks like a regular expression, the text for which you want to search is, itself, going to be viewed as regular expression syntax. Those who studied critical thinking at college will already be questioning what to do if the character sequence that is the subject of a search contains one or more of these characters. For example the dot (.) and square brackets () all have special meaning. There is no limit to the number of characters that can be grouped in the brackets when using this filtering technique.Īs you have seen, regular expressions assign special meaning to particular characters. Use of this syntax ensures that only the words 'Grey' and 'Gray will match the search criteria.

For example: mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gry Computer Case' The syntax for this requires that the characters be places in square brackets at the desired location in the match text.

Fortunately, regular expressions allow us to specify a group of acceptable character matches for any character position. In reality we are only interested in words that contain either an 'a' or an 'e' in that location. One problem with the approach outlined above is that any letter between the 'Gr' and the 'y' would have registered as a match. Regular expressions, however, do not stop here. So far we haven't done anything that could not have been achieved using wildcards. | 12 | Gray Computer Case | ATX PC CASE (USA) | | 11 | Grey Computer Case | ATX PC CASE | For example: mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr.y Computer Case' Rather like the LIKE underscore wildcard, this character indicates that any character in this position in the text will be considered a match. To perform this retrieval we will use the regex dot character matching (.). As in the previous chapter we need to retrieve rows from a table taking into consideration the difference in spelling of the color gray (grey). In order to introduce the REGEXP operator, we will begin by looking at a use of regular expressions that could similarly be used with the LIKE operator.

Mysql like or regexp