CODEWITHSUNDEEP
sqlamazon-redshift
Vivek Sasikumar
Vivek Sasikumar

Reputation: 119

Create a new column that contains a list of substrings from another column

I have the following table with "Column1", "Column2" and "Column3"

Column1                                 Column2  Column3
ABC - Loading                              22   3815
ABC - Loading of Page                      49   5131
Enter -CDE - Any action with target        74   2798
Exit -CDE - Any action with target2        35   3351
ACE Wireless - loading1                    12   9910
All -ACE Wireless - Click1                 49   6060
GATE - click or touch on egh               76   6061
GATE - click or touch on plans             100  6020
HUB - loading of def                       90   4854
All -HUB - Click or Touch on poe           82   2754

I need to create "NewColumn" where I need to extract specific strings from a list(CDE,ACE,GATE,HUB) and lookup against "Column1" as follows:

Column1                                 Column2  Column3  NewColumn
ABC - Loading                              22   3815        ABC
ABC - Loading of Page                      49   5131        ABC
Enter -CDE - Any action with target        74   2798        CDE
Exit -CDE - Any action with target2        35   3351        CDE
ACE Wireless - loading1                    12   9910        ACE
All -ACE Wireless - Click1                 49   6060        ACE
GATE - click or touch on egh               76   6061        GATE
GATE - click or touch on plans             100  6020        GATE
HUB - loading of def                       90   4854        HUB
All -HUB - Click or Touch on poe           82   2754        HUB

Please note that the keywords looked up does not repeat.

I have tried various commands such as EXTRACT, SUBSTRING etc. But none of the commands I looked at works for my specific ask.

I am looking for guidance in RedShift SQL on how I can create "NewColumn" with the list which looks up the keyword from "Column1"

Upvotes: 2

Views: 108

Answers (1)

MatBailie
MatBailie

Reputation: 86775

This will find the first occurrence that matches one of the values in the list given in the question.

select
    Column1,
    Column2,
    Column3,
    regexp_substr(Column1, 'CDE|ACE|GATE|HUB') AS NewColumn
from
    your_table;

EDIT: To accomodate @TimBiegeleisen

select
    Column1,
    Column2,
    Column3,
    regexp_substr(
        Column1,
        (SELECT LIST_AGG(item, '|') FROM table_of_codes)
    ) AS NewColumn
from
    your_table;

Or...

select
    your_table.Column1,
    your_table.Column2,
    your_table.Column3,
    table_of_codes.item
from
    your_table
inner join
    table_of_codes
        on yourTable.col3 LIKE '%' + table_of_codes.item + '%'
;

Or many such variations.

(Where the table table_of_codes holds one row per code you are searching for.)

Upvotes: 1

Related Questions