Regex issue on SQL

Question

Why do I get 0 when running this expression?

SELECT 'Nr. 1700-902-8423. asdasdasd' REGEXP '1+[ ,.-/\]*7+[ ,.-/\]*0+[ ,.-/\]*0+[ ,.-/\]*9+[ ,.-/\]*0+[ ,.-/\]*2+[ ,.-/\]*8+[ ,.-/\]*4+[ ,.-/\]*2+[ ,.-/\]*3+';

I need to get true, when the text contains the specified number (17009028423). There can be symbols ,.-/\ between digits. For example, if I have number 17009028423, I need get true when in text is:

1700-902-8423
17-00,902-84.23
170/09-0.28\423
1700..902 842-3
17,.009028 4//2\3
etc.

Thanks.

Mifeet · Accepted Answer · 2014-01-14 14:58:03Z

3

There are two problems with your regular expression. First is that backslash in \] escapes the special meaning of ] to denote a character class. You need to escape your backslash: \\]. Another problem is that - denotes a range [ and ] (e.g. [a-zA-Z]). You need to escape that too or put it at the end like [a-zA-Z-] (as @tenub said). Plus the backslashes should be escaped themselves, which makes:

SELECT 'Nr. 1700-902-8423. asdasdasd' REGEXP '1[ ,./\\\\-]*7[ ,./\\\\-]*0[ ,./\\\\-]*0[ ,./\\\\-]*9[ ,./\\\\-]*0[ ,./\\\\-]*2[ ,./\\\\-]*8[ ,./\\\\-]*4[ ,./\\\\-]*2[ ,./\\\\-]*3'

You can check for yourself. I also removed + signs in case you want to match each number only once.

edited Jan 14, 2014 at 14:58

answered Jan 14, 2014 at 14:51

Mifeet

13.8k6 gold badges62 silver badges110 bronze badges

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

2 Comments

tenub Over a year ago

No need to escape -. Just put it at the end inside the []. ;)

OGHaza Over a year ago

We could really confuse things and use the range [,-/] for [,./-]

Collectives™ on Stack Overflow

Regex issue on SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related