CODEWITHSUNDEEP
sqlpostgresql
Edamame
Edamame

Reputation: 25366

PostgreSQL: transforming a column based on column value

I want to transform col_A in my table based on the values like below:

col_A
----------------------------
Hello_axd_sdc_we_world
Hello_g_world
Hello_world
Goodbye_A
Goodbye_sdg_Sda
Goodbye
Goodbye_asd_asd_Sddg

I would like the transformed column d_col_A looks like:

col_A                            d_col_A
-----------------------------------------
Hello_axd_sdc_we_world           Hello_world 
Hello_g_world                    Hello_world 
Hello_world                      Hello_world 
Goodbye_A                        Goodbye
Goodbye_sdg_Sda                  Goodbye
Goodbye                          Goodbye
Goodbye_asd_asd_Sddg             Goodbye

And here is my rules:

If col_A starts with Hello and end with World
   Then d_col_A = Hello_Wolrd
If col_A starts with Goodbye
   Then d_col_A = Goodbye

Is this something possible? Thanks!

Upvotes: 3

Views: 428

Answers (1)

Twelfth
Twelfth

Reputation: 7180

Create a table called 'lookup' or something to that extent as ID,first_param,second_param,translate_value

1,hello,world,hello_world
2,goodbye,null,goodbye

Now some join fun. Postgres has a left() and right() and length() function that we can make use of here. more complicated rule sets could also include a like function (third parameter = must contain this word?) if desired.

Select a.col_A , l.translate_value
from table_a a
left join lookup_table l
  on left(a.col_a,length(l.first_param)) = l.first_param
  and (right(a.col_a,length(l.second_param)) = l.second_param or l.second_param is null)

I did a left join so nulls get produced when the rules aren't met rather than dropping lines. I lack a postgres test environment, but syntax should be right.

Case version

select case when left(col_a,5) = hello and right(col_a,5) = 'world' then 'hello world' 
case when left(col_a,7) = 'goodbye' then goodbye
else 'no clue what this means'
from table_a

Upvotes: 1

Related Questions