How to add a 1 to a phone number and remove the dashes?

Question

I need the value of my data to look like this 18184789564.

I used this code:

REGEXP_REPLACE(+1, b.PHONE_NUMBER, '[^0-9]', '') AS PHONE_NUMBER 

I get an error

Numeric value is not recognized

Hopefully I can just add as simple as 1 because I was able to removed the dashes already.

Answer 1

Snowflake uses the CONCAT() function for string concatenation. You have to do the concatenation with the result of REGEXP_REPLACE().

CONCAT('1', REGEXP_REPLACE(b.PHONE_NUMBER,'[^0-9]','')) AS PHONE_NUMBER