Portfolio Gallery Polski

Tag "regexp"

MySQL - Regular expressions

Meta-chars of regular expressions for MySQL

Although many languages lets to use regular extensions that, this chars can be used for specified programing languages. Here is list of chars for MySQL.

• . - any char
• ^ - begin of sequence
• $ - end of sequence
• [...] - any char form collection (...)
• [^...] - any char from out of collection (...)
• [:k:] - any char from class k
• a* - number of appears char a is 0 or more
• a+ - number of appears char a is 1 or more
• a? - number of appears char a is 0 or 1
• a{5} – number of appears char a is 5
• a{1,5} – number of appears char a is between 1 and 5
• a{5,} - number of appears char a is 5 or more
• a|b – appear char a or b
• (...) - define of store extension

To inform MySQL database about use in query regular extensions should be used command REGEXP. MySQL database doesn't distinguish chars size inside query, so decision about using small caps or all caps while creating query depends on user. There are some cases when char to find is a part of regular extension (ex. Searching char [). In this case before use of char should be added symbol \ (ex. \[).

{newpage}

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]))';

About me

My name is Tomasz Chudyk and welcome to my site. I'm just a “poor student” form Poland :). I'm interested in open-source movement, Linux and web technologies. I like watching movies in the cinema and listening to good music. (read more →)

Calumma Player

Calumma Player, is a multimedia player basing on Xine engine and writing in Java. Actually is not available yet, but soon everyone can try it (I hope so) and if one likes it, one can use it. (read more →)

GoRapidDownloader

GoRapidDownloader is a small program written in Java and is used for downloading files from popular website - RapidShare (read more →)

jMPD

jMPD is simple MPD (Music Player Daemon) client written in Java. (read more →)