Using REGEXP in MYSQL Query

September 14, 2007 at 5:33 am | Posted in tutorial | Leave a comment

Regular Expression (REGEXP) is a “powerful way of specifying a pattern for a complex search”. REGEXP is a set of string that describes what you’re looking for.

Here are the operators of REGEXP that can be used for the following special characters and
constructs:
1. (^) – match the beginning of the string
Example:
To search for a name that starts with ‘m’ in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘^m’)”);

Output: All the names that begins with letter ‘m’.{mariecon,marrel,may}

2. ($)- match the end of the string
Example:
To search for a name that ends with ‘e’ in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘y$’)”);

Output: All the names that ends with letter ‘y’.{may,lenny,mary}

3. (.) – match any character
Example:
To search for a name that has only 3 letters in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘^…$’)”);

Output: All the names that have only three letters (no more no less).{ann,mac,joe}

4. (*) – match zero or more character specified before it
Example:
To search for a name that has no or more letter ‘a’ as the 2nd letter in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘^ca*’)”);

Output: All the names that that have or does not have ‘a’ as a second letter .{carie,cydrick,charisse}

5. (+) – match one or more character specified before it
Example:
To search for a name that has one or more letter ‘a’ as the 2nd letter in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘^an+’)”);{anie,anne,anthony}

6. (?) – match either zero or one character specified before it
Example:
To search for a name that has no or one letter ‘a’ as the 2nd letter in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘^me?’)”);{mercy,myra,melanie}

7. (|) – either of the character/s match specified before and after it
Example:
To search for a name that begins with ‘john’ OR ‘jan’ in field name in the table info.
my_qry = mysql_query(“SELECT name FROM info WHERE name REGEXP (‘john|jon’)”);{johnross,carljohn,merjonie}

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: