CODEWITHSUNDEEP
mysqlregex
user4069489
user4069489

Reputation:

Mysql regexp easy

I want to get all lines that according to the pattern xxx.xxx (only) where x is a number from 1 to 9 or any letter

My expression does not work REGEXP '[a-z]{1,3}\.[a-z]{1,3}'

Thx!

UPD: Thx fo all, but does not work! I tryed so

SELECT email, registered, voted, ip, agent
FROM  `voters` 
WHERE member =199
AND DATE_FORMAT( voted,  '%Y-%m-%d' ) <  '2014-09-23'
AND SUBSTRING_INDEX( email,  '@', 1 ) 
REGEXP  '[a-zA-Z1-9]{3}\.[a-zA-Z1-9]{3}'

result:

libsherbakul@...
savelkina_061080@...
dreik_339@...
simauta25@....
suslenkovagtp@...
driamoff@...
anashkina_1966@...
fedotovauliya@...
omsklib@...

UPD 2: Itryed change pattern so '[a-zA-Z1-9]{3}[\.]{1}[a-zA-Z1-9]{3}' RESULT: 

regina.zheyli@...
rus.antonin@...
kira.albenina@...
alya.oktya@...
andryushka.gromov.1986@...
inna.festival@...
ilyubaj.baktybaev@...
vika.korotkova.02@...
silina.lika@...
mika.sidorova@...
kharlov.dmitriy@...
toni.gordon.70@...
vasya.ganichev.74@...
fomin.slavick22@...

No good, becouse more than 3 symbols before and after "."

Upvotes: 0

Views: 53

Answers (3)

VMai
VMai

Reputation: 10336

If you want exactly three letters or digits followed by a dot, followed by exactly three characters with the same conditions and not more before and after then you've got to use 

REGEXP '^[a-zA-Z1-9]{3}\.[a-zA-Z1-9]{3}$'

The special characters ^ and $ have this meaning:

character     meaning
----------------------------------------------
    ^         Match the beginning of a string.
    $         Match the end of a string.

Upvotes: 1

aelor
aelor

Reputation: 11116

should be 

[a-zA-Z1-9]{3}\.[a-zA-Z1-9]{3}

to allow 3 only on both sides of period.

and any letter as you said.

Upvotes: 0

falsetru
falsetru

Reputation: 368954

[a-z] matches only lowercase alphabet. You need to specify uppercase, digits 1-9 too.

And the quantifier should be modified. {1-3} matches 1 to 3 times of preceding pattern. Use {3} to match 3 times.

REGEXP  '[a-zA-Z1-9]{3}\.[a-zA-Z1-9]{3}'

Upvotes: 0

Related Questions