1 / 13

SQL

SQL. Regular Expressions. REGEXP. Mysql supports regular expressions Keyword is REGEXP Select columns from tableName where columnName regexp ‘someRegularExpression’. REGEXP.

alden-gallagher
Download Presentation

SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Regular Expressions

  2. REGEXP • Mysql supports regular expressions • Keyword is REGEXP • Select columns from tableName where columnName regexp ‘someRegularExpression’

  3. REGEXP • Concept and syntax are somewhat similar to the regular expression concept and syntax in various programming languages (Perl, PHP, Java, ..)

  4. REGEXP • Customers having a p in their name • Select * from customer where name regexp ‘p’; • Customers containing pre in their name • Select * from customer where name regexp ‘pre’;

  5. REGEXP • Use of meta-characters enable more powerful SQL queries • [ ]  in this set • Customers who have a, b, or c in their name • Select * from customer where name regexp ‘[abc]’;

  6. REGEXP • [^ ]  not in this set • Customers who have a character that is not a to x in their name • Select * from customer where name regexp ‘[^a-x]’;

  7. REGEXP • ^  matches at beginning • $  matches at the end • .  matches any character • *  0 or more • Customers who have a name that starts with a letter between d and z and ends with e • Select * from customer where name regexp ‘^[d-z].*e$’;

  8. REGEXP • Do not confuse • ^ inside the square brackets, i.e. [^ ] not in set • With • ^  matches at the beginning

  9. REGEXP • ?  0 or 1 • +  1 or more • ab | xyz  matches ab or xyz • Customers who have a name that starts with a or c • Select * from customer where name regexp ‘^(a|c)’;

  10. REGEXP • {n}  n instances of character • {m, n}  between m and n (included) instances of character • Customers who have a name that contains 2 consecutive s • Select * from customer where name regexp ‘s{2}’;

  11. REGEXP • [a-z] or [[:alpha:]]  matches a letter • [0-9] or [[:digit:]]  matches a digit • Customers who have an address that contains a digit • Select * from customer where address regexp ‘[0-9]’;

  12. REGEXP • [[:character_class:]]  matches a character belonging to the class character_class • Character_class could be: digit, alpha, alnum, space, blank, lower, upper, .. • http://dev.mysql.com/doc/refman/5.0/en/regexp.html

  13. REGEXP • Customers from a city that has a white space character in it • Select * from customer where city regexp ‘[[:space:]]’; • Could also use: • Select * from customer where city regexp ‘ ’;

More Related