MySQL Regular Expressions

2010-08-27  来源:本站原创  分类:Database  人气:176 

Never used mysql before the regular expression, this article is taken from the MySQL 5.1 manual in Chinese .

MySQL pattern matching provided by the other type is the use of extended regular expressions. When you test for such pattern matching, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, they are synonyms).

Extended regular expressions some characters are:

* '.' Matches any single character.

* Character class in square brackets "[...]" match any character. For example, "[abc]" matches "a", "b" or "c". In order to name the range of characters, using a "-." "[Az]" matches any letter, whereas "[0-9]" matches any number.

* "*" Matches zero or more characters in front of it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of figures, and the ".*" matches any number of any characters.

  • If REGEXP mode and the value being tested anywhere match pattern to match (which is different from the LIKE pattern matching, and only with the values match, pattern match only).
  • To locate a model to be tested so that it must match the value of the beginning or end of the beginning of the model using the "^" or the end of the mode of "$."

To demonstrate how extended regular expressions work, the following rewrite using REGEXP LIKE queries shown above:

In order to find out with "b" at the beginning of the name, use "^" matches the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

If you want to force the REGEXP is case-sensitive comparison, use the BINARY keyword to make one of the string into a binary string. The name of the first query matches only lowercase letters 'b'.

mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

In order to find out with "fy" at the end of the name, use "$" matches the name of the end:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

To find out contains a "w" in the name, use the following query:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

As if a regular expression anywhere in the value, the pattern matching, and you do not have the query in the previous model on both sides in place a wildcard to make it match the whole value, just like you used a SQL pattern so.

To find out exactly five characters contain the name, use "^" and "$" matches the name of the start and end, and 5 "." Instances in between:

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

You can also use the "(n)" "repeat n times" operator rewrite the previous query:

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
 Appendix  G:MySQL Regular expressions    Provides a regular expression syntax details  .
相关文章
  • MySQL Regular Expressions 2010-08-27

    Never used mysql before the regular expression, this article is taken from the MySQL 5.1 manual in Chinese . MySQL pattern matching provided by the other type is the use of extended regular expressions. When you test for such pattern matching, use th

  • MySql description of regular expressions 2010-03-31

    Regular expression (regex) is a complex query defined a powerful tool. Here is a simple information, it ignores some detailed information. Regular expression defines a string of rules. The simplest regular expression does not contain any reserved wor

  • mysql using regular expressions 2011-02-17

    Role is to match the regular expression this side, a pattern (regular expression) with a text string to compare. MySQL with the WHERE clause of the regular expression provided the initial support that allows you to specify a regular expression filter

  • MySQL application of regular expressions 2011-03-19

    A regular expression is specified for the complex pattern of powerful search methods. MySQL uses Henry Spencer's implementation of regular expressions, the goal is in line with POSIX 1003.2. MySQL uses the extended version to support SQL statements u

  • mysql regular expression 2010-09-16

    Regular expression (regex) is a complex query defined a powerful tool. Here is a simple information, it ignores some detailed information. Regular expression defines a string of rules. The simplest regular expression does not contain any reserved wor

  • MYSQL-Chinese retrieval matching and regular expressions 2010-10-12

    Today, doing a search for mysql that I used when select name from contact where name like '% a%' when the result of the addition contains the name of a Chinese foreign even includes the "new" name also appears in search results, there This makes

  • oracle regular expressions (10G can) 2011-06-01

    oracle regular expression (regular expression) briefly present, the regular expression has a lot of software is widely used, including * nix (Linux, Unix, etc.), HP and other operating systems, PHP, C #, Java and other development environment. Oracle

  • oracle regular expressions used (10G can) 2011-06-01

    oracle regular expression (regular expression) briefly present, the regular expression has a lot of software is widely used, including * nix (Linux, Unix, etc.), HP and other operating system, PHP, C #, Java and other development environments. Oracle

  • Detailed JS regular expressions 2009-02-27

    Opening, or have to talk about ^ and $ that they are separately used to match the beginning and end of the string, the following examples to illustrate separately "^ The": there must be at the beginning of "The" string; "of despai

  • JS regular expressions testing and testing tools JS 2009-03-14

    <! DOCTYPE HTML PUBLIC "- / / W3C / / DTD HTML 4.01 Transitional / / EN"> <HTML> <HEAD> <TITLE> Javascript regex test page </ TITLE> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=GB23

  • JS regular expressions 2009-05-03

    / / Check whether the figure composed entirely of Code function isDigit (s) ( var patrn = / ^ [0-9] (1,20) $ /; if (! patrn.exec (s)) return false return true ) / / Check login name: You can only enter the letter at the beginning of 5-20 months, may

  • Commonly used in JavaScript regular expressions to verify 2009-07-01

    The correct format: "01" "09" and "1" "31." Chinese characters matching the regular expression: [u4e00-u9fa5] Matching double-byte characters (including Chinese characters included): [^ x00-xff] Blank lines matching

  • javascript regular expressions review 2009-10-21

    JS made a recent project, have seen things that basically do not remember that the Internet is to find some information on the record about. Javascript regular expression of a basic knowledge of 1 javascript regular object creation and usage Javascri

  • java regular expressions to achieve 2010-02-13

    Nepal may be encountered at work need to obtain one or a number of variables inside the http response, this time the role of regular expressions are embodied, and in general work, a lot of use to the regular expressions. Here I use the java code is g

  • eclipse Using regular expressions to replace 2010-03-29

    At work, often encountered refactoring code, and some things obviously is very simple, but want to change things too much, too much duplication of effort, so learn some techniques are often used first gray Person.java in these static constant, note t

  • Extract images from the web address (java regular expressions) 2010-03-29

    /** * Get the address of the picture in a Web page */ public static List<String> getImgStr(String htmlStr){ String img=""; Pattern p_image; Matcher m_image; List<String> pics = new ArrayList<String>(); String regEx_img = "

  • Regular expressions - the basics explained 2010-03-29

    [Original article, please retain or specify reproduced Source: http://www.regexlab.com/zh/regref.htm] Introduction regular expression (regular expression) is to use a "string" to describe a feature, and then to verify that the other "string

  • Regular Expressions 2010-03-29

    First, an overview of 1, regular expressions can be said to have provided any kind of programming language mechanisms, it mainly provides string processing capabilities. 2, regular expressions in the page address in the usage scenarios: 1) Forms Auth

  • java regular expressions 2010-03-29

    We all know, in the program development, will inevitably encounter the need match, find, replace, to determine the string to happen, but sometimes these situations more complex, if the pure coding solution, often a waste of programmer time and effort

  • Regular Expressions reproduced 2010-03-29

    http://gaobusi.javaeye.com/blog/551807 import java.util.regex.Matcher; import java.util.regex.Pattern; public class Test ( public static void main (String [] args) ( / / A simple understanding of the concept of regular expressions / * p ( "abc".