'Snowflake SQL - Format Phone Number to 9 digits

I have a column with phone numbers in varchar, currently looks something like this. Because there is no consistent format, I don't think substring works.

(956) 444-3399 964-293-4321 (929)293-1234 (919)2991234

How do I remove all brackets, spaces and dashes and have the query return just the digits, in Snowflake? The desired output:

9564443399 9642934321 9292931234 9192991234

sqlsnowflake-cloud-data-platform


Solution 1:[1]

You can use regexp_replace() function to achieve this:

REGEXP_REPLACE(yourcolumn, '[^0-9]','')

That will strip out any non-numeric character.

Solution 2:[2]

You could use regexp_replace to remove all of the special characters

something like this

select regexp_replace('(956) 444-3399', '[\(\) -]', '')

Solution 3:[3]

An alternative using translate . Documentation

 select translate('(956) 444-3399', '() -', '')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 JNevill
Solution 2 Brandon Coleman
Solution 3 Phil Coulson

Related Questions