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
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.
On Wed, Nov 13, 2019 at 10:37 AM Geoff Winkless <pgsqladmin@geoff.dj> wrote:
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
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
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.-