MySQL - Regular expressions

Author: tom000
Date: 2007-03-06 17:19:59

Regular expressions give us more possibilities in scope of searching records in database. They offer much of flexibility while searching pattern and serve to precise describe sequence of chars. They can be found in majority of programing languages and also in databases. MySQL database has this possibility too. Sequence of chars can be characterized by some meta-chars.

Samples of use regular extensions

Using regular extensions isn't difficult, how this can looks like for beginner users. Here i presents a few issues connected with MySQL queries with regular extensions.

Searching records which end on 'a':

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP 'a$';

Searching records which begin on a digit:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '^[:0-9:]';

Searching records with sequence "jak"

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP 'jak';

Searching records which first word begins on 's':

SELECT id,txt2 FROM test.test 
 WHERE txt2 REGEXP '^([[:<:]]s)';

Searching records longer than one word:

SELECT id,txt2 FROM test.test 
 WHERE txt2 REGEXP '^.+ ';

Searching records with website addresses:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '(http://)+[a-z0-9]+(\.[a-z0-9])*(\.[a-z])';

Searching records with email addresses:

SELECT id,txt1 FROM test.test 
 WHERE txt1 REGEXP '([a-z0-9]+@{1}[a-z0-9]+(\.[a-z][0-9])*(\.[a-z]))';

« 1 [2] »

Login :
Website :
Comment :

Image text :

Samples of use regular extensions

Comments

Add comment