Convert regexp query to SQL Server

Question

I am trying to convert this query to SQL Server. It basically removes all non numbers except + in the phone number so I can match it with +19123123123.

select phone
from users
where regexp_replace(phone, '[^+0-9]', '') = '+19123123123'

For example:

19123123123
+1 912 123 123
1 912 123 123
(+1) 912 123 123
(+1)912-123-123

Thanks.

Gordon Linoff · Accepted Answer · 2021-01-07 02:33:36Z

1

This is rather tricky in SQL Server. But one way that comes close is:

select v.*, replace(replace(replace(replace(replace(phone, ',', ''), ')', ''), '(', ''), ' ', ''), '-', '')
from (values ('19123123123'),
             ('+1 912 123 123'),
             ('1 912 123 123'),
             ('(+1) 912 123 123'),
             ('(+1)912-123-123')
     ) v(phone)
where try_convert(decimal(20, 0), replace(replace(replace(replace(replace(phone, ',', ''), ')', ''), '(', ''), ' ', ''), '-', '')) = +19123123123

Here is a db<>fiddle.

This isn't perfect, but it comes pretty close in SQL Server.

edited Jan 7, 2021 at 2:33

answered Jan 7, 2021 at 1:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Kelvin Over a year ago

thanks, i tried your query but it returns no result for me. where phone = '+19123123123' has a result. is there anything i am missing? i tried your solution with a varchar but getting 'Arithmetic overflow error converting varchar to data type numeric.'

Collectives™ on Stack Overflow

Convert regexp query to SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related