Mysql Regular Expressions

  • Filter
  • Time
  • Show
Clear All
new posts

    Mysql Regular Expressions

    In this article, I introduce you to what is known as MySQL Regular Expressions. At the end of the article, I give you a hyperlink to where you will have details on the subject, free.

    The SQL SELECT Statement
    Consider the following database table:

    | name | owner | species | sex | birth | death |
    | Buffy | Harold | dog | f | 1999-05-13 | NULL |
    | Bowser | Diane | dog | m | 1999-08-31 | 2005-07-29 |
    | Fluffy | Harold Taylor | cat | f | 2003-02-04 | NULL |
    | Claws | Gwen Jones | cat | m | 2004-03-17 | NULL |
    | Whistler | Gwen | bird | NULL | 2007-12-09 | NULL |

    Let the name of the table be, Pets. The first column has the name of the pet and the second has the name of the owner of the pet. This table has data about pets and the people who own them. The following SELECT statement would select the first and second rows:

    SELECT * from Pets where species = "dog";

    Reason for Regular Expressions
    You know how to select rows, when the string, e.g. "dog" above, matches the value of a column cell in the table. In the above select statement, "dog" matches the first and second cell values in the third column of the table. You know how to do this kind of thing, already. What about the case when you want the rows, where the name (cell value) of the species has a 'd' ? In the table, the species names with a 'd' are "dog" and "bird". The first letter in "dog" is 'd' and the last letter in "bird" is also 'd'. In other words, you want the first, second and last rows. The following select statement will select these 3 rows, matching only the character, 'd' in the third column.

    SELECT * from Pets where species rLike ".*d.*";

    In this statement, ".*d.*" , without the quotes, is an example of what is called, a pattern. It means, any string that has the letter, 'd'. rLike means, like. So the statement selects rows where species are like ".*d.*".

    A pattern is a set of characters such as ".*d.*" , that determines what is matched in a string. In the above table and in the third column, "dog" and "bird" are matched. "dog" is selected from the two instances of "dog" and "bird" is selected from the single instance of "bird". There are five choices to choose from, in the third column. The choices are: "dog" (twice), "cat" (twice) and "bird" (once). Each of these choices is a string. Such a string is called, a subject. Note: It is possible for you to have a table of only one row with just one subject. In the above case there are 5 subjects. So in a regular expression situation, you have the subject, the reserved word, rLike, and the pattern. Note: rLike is an operator similar to the assignment operator, = .

    The pattern is made up of ordinary characters and Metacharacters. These characters are combined in a special way to form an expression. So you can call a pattern, a regular expression, or simply, regex. You will see how patterns are formed (constructed) soon.

    All what I have said above, is just the tip of the iceberg. There are more interesting things to come, on MySQL Regular Expressions, that I have prepared for you. Some of the above stuff is repeated there for emphasis. To reach that, click the following hyperlink:

    Mysql Regular Expressions