Hey, Would you like to work at Home ?? Just click here No need to pay, just register free and activate your account and get data Entry Work at your Home.

Tuesday, January 20, 2009

MySQL Regular Expressions - Part 1


A regular expression (regex) is a powerful way of specifying a complex search.



MySQL uses Henry Spencer's implementation of regular
expressions, which is aimed at conformance with POSIX
1003.2. MySQL uses the extended version.



This is a simplistic reference that skips the details. To get more exact
information, see Henry Spencer's regex(7) manual page that is
included in the source distribution. See section C Credits.



A regular expression describes a set of strings. The simplest regexp is
one that has no special characters in it. For example, the regexp
hello matches hello and nothing else.



Non-trivial regular expressions use certain special constructs so that
they can match more than one string. For example, the regexp
hello|word matches either the string hello or the string
word.



As a more complex example, the regexp B[an]*s matches any of the
strings Bananas, Baaaaas, Bs, and any other string
starting with a B, ending with an s, and containing any
number of a or n characters in between.




Your Ad Here


A regular expression may use any of the following special
characters/constructs:



^

Match the beginning of a string.

 
mysql> SELECT "fo\nfo" REGEXP "^fo$"; -> 0
mysql> SELECT "fofo" REGEXP "^fo"; -> 1


$

Match the end of a string.

 
mysql> SELECT "fo\no" REGEXP "^fo\no$"; -> 1
mysql> SELECT "fo\no" REGEXP "^fo$"; -> 0


.

Match any character (including newline).

 
mysql> SELECT "fofo" REGEXP "^f.*"; -> 1
mysql> SELECT "fo\nfo" REGEXP "^f.*"; -> 1


a*

Match any sequence of zero or more a characters.

 
mysql> SELECT "Ban" REGEXP "^Ba*n"; -> 1
mysql> SELECT "Baaan" REGEXP "^Ba*n"; -> 1
mysql> SELECT "Bn" REGEXP "^Ba*n"; -> 1


a+

Match any sequence of one or more a characters.

 
mysql> SELECT "Ban" REGEXP "^Ba+n"; -> 1
mysql> SELECT "Bn" REGEXP "^Ba+n"; -> 0


a?

Match either zero or one a character.

 
mysql> SELECT "Bn" REGEXP "^Ba?n"; -> 1
mysql> SELECT "Ban" REGEXP "^Ba?n"; -> 1
mysql> SELECT "Baan" REGEXP "^Ba?n"; -> 0


de|abc

Match either of the sequences de or abc.

 
mysql> SELECT "pi" REGEXP "pi|apa"; -> 1
mysql> SELECT "axe" REGEXP "pi|apa"; -> 0
mysql> SELECT "apa" REGEXP "pi|apa"; -> 1
mysql> SELECT "apa" REGEXP "^(pi|apa)$"; -> 1
mysql> SELECT "pi" REGEXP "^(pi|apa)$"; -> 1
mysql> SELECT "pix" REGEXP "^(pi|apa)$"; -> 0



Your Ad Here

(abc)*

Match zero or more instances of the sequence abc.

 
mysql> SELECT "pi" REGEXP "^(pi)*$"; -> 1
mysql> SELECT "pip" REGEXP "^(pi)*$"; -> 0
mysql> SELECT "pipi" REGEXP "^(pi)*$"; -> 1


{1}

{2,3}

The is a more general way of writing regexps that match many
occurrences of the previous atom.


a*

Can be written as a{0,}.
a+

Can be written as a{1,}.
a?

Can be written as a{0,1}.

To be more precise, an atom followed by a bound containing one integer
i and no comma matches a sequence of exactly i matches of
the atom. An atom followed by a bound containing one integer i
and a comma matches a sequence of i or more matches of the atom.
An atom followed by a bound containing two integers i and
j matches a sequence of i through j (inclusive)
matches of the atom.

Both arguments must be in the range from 0 to RE_DUP_MAX
(default 255), inclusive. If there are two arguments, the second must be
greater than or equal to the first.
[a-dX]

[^a-dX]

Matches
any character which is (or is not, if ^ is used) either a, b,
c, d or X. To include a literal ] character,
it must immediately follow the opening bracket [. To include a
literal - character, it must be written first or last. So
[0-9] matches any decimal digit. Any character that does not have
a defined meaning inside a [] pair has no special meaning and
matches only itself.


Your Ad Here

 
mysql> SELECT "aXbc" REGEXP "[a-dXYZ]"; -> 1
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]$"; -> 0
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1
mysql> SELECT "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0
mysql> SELECT "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1
mysql> SELECT "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0


[[.characters.]]

The sequence of characters of that collating element. The sequence is a
single element of the bracket expression's list. A bracket expression
containing a multi-character collating element can thus match more than
one character, for example, if the collating sequence includes a ch
collating element, then the regular expression [[.ch.]]*c matches the
first five characters of chchcc.

[=character_class=]

An equivalence class, standing for the sequences of characters of all
collating elements equivalent to that one, including itself.

For example, if o and (+) are the members of an
equivalence class, then [[=o=]], [[=(+)=]], and
[o(+)] are all synonymous. An equivalence class may not be an
endpoint of a range.

[:character_class:]

Within a bracket expression, the name of a character class enclosed in
[: and :] stands for the list of all characters belonging
to that class. Standard character class names are:







Name Name Name
alnum digit punct
alpha graph space
blank lower upper
cntrl print xdigit


These stand for the character classes defined in the ctype(3) manual
page. A locale may provide others. A character class may not be used as an
endpoint of a range.


Your Ad Here

 
mysql> SELECT "justalnums" REGEXP "[[:alnum:]]+"; -> 1
mysql> SELECT "!!" REGEXP "[[:alnum:]]+"; -> 0


[[:<:]]

[[:>:]]

These match the null string at the beginning and end of a word
respectively. A word is defined as a sequence of word characters which
is neither preceded nor followed by word characters. A word character is
an alnum character (as defined by ctype(3)) or an underscore
(_).

 
mysql> SELECT "a word a" REGEXP "[[:<:]]word[[:>:]]"; -> 1
mysql> SELECT "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -> 0





 
mysql> SELECT "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -> 1


1 comment:

Anonymous said...

This is very good information. It is usefully for beginners like me.

Thanks

Your Ad Here