MySQL regexp select

Question

I feel very lost in solving one simple (I guess) task. I have a table which contains links in the following format: /text(or any character)/, /text(or any character)/text1(or any character), http://domain.tld/text/text1 and so on... I want to list (select) only the links which are in the following format: /text(or any character)/text1(or any character) I tried with the following rules:

select href from table
where href not like '%http%'
and href REGEXP '^[/][a-zA-Z](/)[a-zA-Z]'

Empty set (0.00 sec)

select href from table
where href not like '%http%'
and href REGEXP '^[/][a-z](/)%';

Empty set (0.00 sec)

Kindly help with that. Thank you!

Your regex only allows for one character as text. Try ^[/][a-zA-Z]+(/)[a-zA-Z]+ and ^[/][a-z]+(/). — SamWhan
– SamWhan, Commented Jan 18, 2017 at 14:07
Hello and thank you very much for the rules. I tried both of them and now the result is getting better however I do select the following links as well with your rules: /text/text1/text2 — Pavel Nikolaev
– Pavel Nikolaev, Commented Jan 18, 2017 at 14:15
That's because the regexp's don't check for the end of the string. If it matches up to the end of the regex, it's considered a hit. Adding a $ to the end of it makes sure the end of the regex matches the end of the string. But Wiktors answer handles all that. You should use his solution (he's the maestro ;). — SamWhan
– SamWhan, Commented Jan 18, 2017 at 14:36

Wiktor Stribiżew · Accepted Answer · 2017-01-18 14:09:09Z

4

To only find entries that start with /, then have 1+ chars other than / and then again this pattern and the end of string, you need to use a REGEXP with anchors and negated bracket expressions:

REGEXP '^/[^/]+/[^/]+$'

or, contracting it with a range/limiting quantifier:

REGEXP '^(/[^/]+){2}$'

See the regex demo

Details:

^ - start of string
/ - a literal /
[^/]+ - 1+ chars other than /
/[^/]+ - same as above
$ - end of string.

answered Jan 18, 2017 at 14:09

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

MySQL regexp select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related