11 Vote

MySQL: Regular Expressions in MySQL queries

Info by Sledge | 2012-06-17 at 16:02

What many do not know: In MySQL queries, you can also use regular expressions. I for myself am always amazed of what is possible with MySQL.

Here's an example of how to use a regular expression in a MySQL query:

SELECT * FROM tab WHERE firstname REGEXP '^A';
SELECT * FROM tab WHERE firstname NOT REGEXP '^(A|B|C)';
SELECT * FROM tab WHERE number REGEXP '[1-5]';
SELECT * FROM tab WHERE number REGEXP '^[0-9]{5}$';

The first line will search for all records in which the value of the column "first name" begins with an "A". The next query finds all records where the value in "firstname" begins not with an "A", "B" or "C". The query in the third line will search for all records which have a 1, 2, 3, 4 or 5 in the column "number" and the last statement will search for all records whose column "number" contains a 5-digit number consisting of the digits 0 to 9.

As you can see, you can use the common usage formulations for regular expressions, and thus search much more targeted for specific data in your MySQL database.

Warning

In the application of regular expressions in MySQL caution should be exercised. Regular expressions go rather on the performance, as there is pretty much to calculate and MySQL cannot use any indexes for the search. You should always keep that in mind when applying.

ReplyPositiveNegative

About the Author

AvatarThe author has not added a profile short description yet.
Show Profile

 

Related Topics

HTACCESS: Simplify URL

Tutorial | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.