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.

Answer 1

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.