Can TRIM function in SQL be more precise?

user_12854744 0
Viewed 98

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

trim postgresql sql
3 Answers

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.

user_11440350

One of the options... Maybe it will be ok for you:

with trimed as
(select substring(host_media from 2 for length(host_media)-2) as clean_col
from ImportH
)
insert into Media (media)
select unnest(string_to_array(clean_col, ',')) 
from trimed;

Here is a demo

I have understood the "floating commas" term little bit late so I added some changes to my query:

with trimed as
(select replace(substring(host_media from 2 for length(host_media)-2), '''', '') as clean_col
 from ImportH
)
insert into Media (media)
select ltrim(unnest(string_to_array(clean_col, ','))) 
from trimed;
user_65650380

Here is a different approach which uses json functions.

First create media table;

CREATE TABLE media AS (
  WITH my_medias AS (
    SELECT
        jsonb_array_elements_text(jsonb_agg(DISTINCT medias ORDER BY medias)) media
      FROM impor_th, jsonb_array_elements_text(replace(host_media, '''', '"')::jsonb) medias
  )
  SELECT
      row_number() OVER () media_id,
      *
    FROM my_medias
);

Then create many-to-many relation table;

CREATE TABLE media_host AS (
  WITH elements AS (
    SELECT
        id,
        jsonb_array_elements_text(replace(host_media, '''', '"')::jsonb) media
      FROM impor_th i
  )
  SELECT
      e.id AS impor_th_id,
      m.media_id
    FROM elements e
    JOIN media m ON m.media = e.media
);
user_35576760
Related