How to add a hyphen after an nth character in Snowflake

Question

I'm trying to add a hyphen in a specific location to a value in Snowflake. I want the data to look like this:

1-212-1234567- --> 1-212-123-4567-

I tried typing this as: INSERT(OFFICEPHONE, 9, 1, '-') . However, I'm seeing that INSERT is overwriting the 9th character. Is there a different statement that I should use? Thanks!

Answer 1

When we use the INSERT function, it overwrites the character at the specified position so I suggest you to use the combination of CONCAT and SUBSTR functions, which allows you to insert a substring without overwriting any characters.

You can try with the given Query.

SELECT 
    CONCAT(
        SUBSTR(OFFICEPHONE, 1, 8), 
        '-', 
        SUBSTR(OFFICEPHONE, 9)
    ) AS FORMATTED_OFFICEPHONE
FROM 
    your_table;

If you are looking to update the table to reflect this change, you can use the given UPDATE statement:

UPDATE your_table
SET OFFICEPHONE = CONCAT(SUBSTR(OFFICEPHONE, 1, 8), '-', SUBSTR(OFFICEPHONE, 9))
WHERE condition;

Please let me know if this helps.