"split_part" To Structure A Column Into A Table?

I am a digital marketing manager and am trying to breakdown my campaigns for sliced analysis. My campaigns are listed in one column as follows. FR-SRC-PLATFORM | MISC {bw} FR-S

Solution 1:

If you familiar with plpgsql:

dropfunction if exists foo(text, out text, out text, out text, out text, out text);
createfunction foo(p_input text, out cola text, out colb text, out colc text, out cold text, out cole text)
  returns record immutable language plpgsql as $$
declare
  p1 text; pi1 int;
  p2 text; pi2 int;
  p3 text;
begin-- String is like part1 | part2 {part3}-- So lets split it to this parts:
  pi1 :=position('|'in p_input);
  pi2 :=position('{'in p_input);

  p1 :=trim(substring(p_input, 1, pi1 -1));
  p2 :=trim(substring(p_input, pi1 +1, pi2 - pi1 -1));
  p3 :=trim(substring(p_input, pi2));

  -- Debug output
  raise info 'p1: %', p1;
  raise info 'p2: %', p2;
  raise info 'p3: %', p3;

  -- Part1 is like field1-field2-field3:
  cola := split_part(p1, '-', 1);
  colb := split_part(p1, '-', 2);
  colc := split_part(p1, '-', 3);
  -- And the rest:
  cold := p2;
  cole := p3;

  return;
end $$;

with t(x) as (values
  ('FR-SRC-PLATFORM | MISC {bw}'),
  ('FR-SRC-M2M sim  | Sim {bw}'),
  ('ES-SRC-IOT | Connectivity {e}'),
  ('ES-SRC-IOT | Connectivity | SIM | USA {e}')
)
select*, (foo(x)).*from t;

Result:

╔═══════════════════════════════════════════╤══════╤══════╤══════════╤══════════════════════════╤══════╗
║                     x                     │ cola │ colb │   colc   │           cold           │ cole ║
╠═══════════════════════════════════════════╪══════╪══════╪══════════╪══════════════════════════╪══════╣
║ FR-SRC-PLATFORM | MISC {bw}               │ FR   │ SRC  │ PLATFORM │ MISC                     │ {bw} ║
║ FR-SRC-M2M sim  | Sim {bw}                │ FR   │ SRC  │ M2M sim  │ Sim                      │ {bw} ║
║ ES-SRC-IOT | Connectivity {e}             │ ES   │ SRC  │ IOT      │ Connectivity             │ {e}  ║
║ ES-SRC-IOT | Connectivity | SIM | USA {e} │ ES   │ SRC  │ IOT      │ Connectivity | SIM | USA │ {e}  ║
╚═══════════════════════════════════════════╧══════╧══════╧══════════╧══════════════════════════╧══════╝

And it will be very simple to adapt the function's logic if you will found yet another case of your data.

Solution 2:

I'd go with regex substring

with e AS ( VALUES  ('FR-SRC-PLATFORM | MISC {bw}')
      ,('FR-SRC-M2M sim  | Sim {bw}')
      ,('ES-SRC-IOT | Connectivity {e}')
      ,('AXE|SPADE|SHOVEL{test}')
)
SELECT column1,substring(column1 from $$^([^|]*)\|$$ ) as a
    ,substring(column1 from $$^[^|]*\|([^{]*)$$ ) as b
    ,substring(column1 from $x$[{](.*)[}]$$x$ ) as c
FROM e;

gives

            column1            |        a         |       b        |  c   
 ------------------------------+------------------+----------------+------
 FR-SRC-PLATFORM | MISC {bw}   | FR-SRC-PLATFORM  |  MISC          | bw
 FR-SRC-M2M sim  | Sim {bw}    | FR-SRC-M2M sim   |  Sim           | bw
 ES-SRC-IOT | Connectivity {e} | ES-SRC-IOT       |  Connectivity  | e
 AXE|SPADE|SHOVEL{test}        | AXE              | SPADE|SHOVEL   | test