Обсуждение: CASE(?) to write in a different column based on a string pattern

Hi,
     I need to create a CASE (I think) statement to check for a string 
pattern, and based on its value, write a substring in a different column 
(alias).
I'm trying to create a COPY statement to port a table into antoher 
database, which has a table with another format (that's why the aliases)

Let's write it in pseudoSQL:

given this

select pattern from tbl;
pattern
----------
foo1234
bar5678
baz9012

That's what I'm trying to achieve

select
     pattern,
         CASE when pattern like 'foo%' then ltrim(pattern, 'bar') as foo
                   when pattern like 'bar%' then ltrim(pattern, 'bar') 
as bar
                   when pattern like 'baz%' then ltrim(pattern, 'baz') 
as baz
         END
from tbl;

|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?

Thanks in advance
Moreno.-

On Wed, 13 Nov 2019 at 16:24, Moreno Andreo <moreno.andreo@evolu-s.it> 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

Il 13/11/19 17:48, Andrew Kerber ha scritto:
> So what you are doing is transforming the table format from vertical 
> to horizontal.  I think you will want to use a union to join the table 
> to itself along with the case statement to produce the output you are 
> looking for.
>
Not precisely, the string pattern is only part of a bigger table (30 
columns in total), what I'm trying to achieve is what Geoff explained, 
just split values in 3 different columns based on the string pattern

Thanks for your time
Moreno.

Il 13/11/19 17:36, Geoff Winkless ha scritto:
>
> 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
>
>
Geoff,
     it worked perfectly!

Thanks a lot!
Moreno.-