Re: CASE(?) to write in a different column based on a string pattern

[Geoff Winkless <pgsqladmin@xxxxxxxx>]

On Wed, 13 Nov 2019 at 16:24, Moreno Andreo <moreno.andreo@xxxxxxxxxx> wrote:
> |foo   |bar  |baz |
>   1234
>              5678
>                          9012
> (hoping text formatting is ok... 1234 should go in column foo, 568 in
> bar and 9012 in baz)
>
> Is it possible?

Simplest way in plain SQL would be individual case statements for each
column, I think.

SELECT pattern,
  CASE WHEN pattern LIKE 'foo%' THEN SUBSTR(pattern, 4) ELSE '' END AS foo
  CASE WHEN pattern LIKE 'bar%' THEN SUBSTR(pattern, 4) ELSE '' END AS bar
  CASE WHEN pattern LIKE 'baz%' THEN SUBSTR(pattern, 4) ELSE '' END AS baz
FROM tbl;

Geoff