Search code examples
regexstringpostgresqlinsertstrip

Remove special characters from string on insert?

I have a field of type character varying. On insert I'd like to strip out special characters. In this particular case I'd like to strip out hyphens from a column of hyphenated strings, hyphen_field"123-456-789" from table_two and insert as "123456789" into non_hyphen_field in table_one. I'm starting with a statement of the following form:

INSERT INTO schema.table_one(var_one,var_two,non_hyphen_field)
SELECT var_one, var_two, hyphen_field
FROM schema.table_two;

What is the cleanest way to accomplish this?

Solution

  • On Postgres you can use replace function.

    select replace('123-456-789', '-','');

    | replace   |
| :-------- |
| 123456789 |

    dbfiddle here