Mysql regexp easy

Question

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 "."

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

Avinash Raj
– Avinash Raj

2014-09-24 13:08:14 +00:00
Commented Sep 24, 2014 at 13:08 — Avinash Raj
– Avinash Raj, Commented Sep 24, 2014 at 13:08
does not work, passes over 3 symbols and few "."

user4069489
– user4069489

2014-09-24 13:23:04 +00:00
Commented Sep 24, 2014 at 13:23 — user4069489
– user4069489, Commented Sep 24, 2014 at 13:23

VMai · Accepted Answer · 2014-09-24 15:25:07Z

1

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.

answered Sep 24, 2014 at 15:25

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

falsetru · Accepted Answer · 2014-09-24 12:26:49Z

0

[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}'

answered Sep 24, 2014 at 12:26

falsetru

371k69 gold badges769 silver badges659 bronze badges

Comments

aelor · Accepted Answer · 2014-09-24 12:27:05Z

0

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.

answered Sep 24, 2014 at 12:27

aelor

11.2k3 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

Mysql regexp easy

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related