Search code examples
sqlpostgresqltrim

Can TRIM function in SQL be more precise?

I have an import table that I want to split into other tables. There's a column in the import table which the data of it's fields have this format: ['email', 'phone', 'facebook'].

In order to separate each value on the field (email, phone and facebook in this case) I'm using the function TRIM when I insert into it's corresponding table, like this:

insert into Media (media)
select distinct trim ('{}' from regexp_split_to_table(host_media, ','))
from ImportH;

But the data inserted into the new table looks dirty, for example in row 1 I would have ['email', in row 2: 'phone' and in row 3: 'facebook'].

How can I make it so the data inserts into the table in a clean way, without the '[' and the floating commas?

I'll provide an image of the import table data of this column and what I get when I split it:

enter image description here

enter image description here

Solution

  • You could just change the splitter:

    select *
from regexp_split_to_table('[''email'', ''phone'', ''facebook'']', '[^_a-zA-Z]+') s(value)
where value <> '';

    The splitters are just whatever characters are NOT valid characters for the strings you want.

    Here is a db<>fiddle.