How to update table ssn values to have dashes?-CodePudding

I have a table called test. there are several records that have unformatted SSNs (i.e. they are missing the dashes)

They are:

123456789

When I want them to be formatted like:

123-45-6789

I want to run a script that can insert in these 2 dashes for records like this that are strings with 9 characters.

CodePudding user response：

If you need to update the rows you can use the following insert() string syntax to check its functionality:

with test as (
  select '123456789' as ssn
)
select insert(insert(ssn,4,0,'-'),7,0,'-')
from test;

update test set ssn=insert(insert(ssn,4,0,'-'),7,0,'-')
where ssn not like '%-%';

It might be a better idea to leave the data as-is and add a generated column that implements the above insert syntax instead. This depends on the data, which you haven't shown us.

CodePudding user response：

A solution that uses the regexp functions

update test 
set SSN = regexp_replace(SSN, '([0-9]{3})([0-9]{2})([0-9]{4})$','\\1-\\2-\\3')
where SSN RLIKE '^[0-9]{9}$'

select * from test

| SSN         |
| :---------- |
| 123-45-6789 |

db<>fiddle here