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.

[phone] 964-293-4321 [phone] (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

Answer

You can use regexp_replace() function to achieve this:

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

That will strip out any non-numeric character.