CODEWITHSUNDEEP
sqlamazon-redshift
Gompu
Gompu

Reputation: 425

Remove character from column based on condition on another column in Redshift sql

I have a table where I want to do the following: If col_1 has values "sakc" or "cosc", remove occurrences of character "_" from those rows of col_2.

Example:

Given table_1

col_1            col_2

sakc             abc_aw
sakc             asw_12
cosc             absd12
dasd             qwe_32
cosc             dasd_1

Desired table_1

col_1            col_2

sakc             abcaw
sakc             asw12
cosc             absd12
dasd             qwe_32
cosc             dasd1

I tried using something along the lines of:

select case when col_1 in ('sakc', 'cosc') then trim("_" from col_2) end col_2 from table_1;

But I am sure it's not the right way and is giving me errors.

Upvotes: 1

Views: 465

Answers (1)

SQLChao
SQLChao

Reputation: 7847

You can use replace()

SELECT  
  col_1
 ,CASE 
    WHEN col_1 in ('sakc', 'cosc') THEN REPLACE(col_2, '_', '')
    ELSE col_2 
  END col2 
FROM table_1;

Upvotes: 4

Related Questions