CODEWITHSUNDEEP
sqlamazon-redshift
Dela
Dela

Reputation: 115

When a statement contains an item in a list, show it in a new column

I would appreciate a little help on some script in sql. So I have a list like the one below and a database table -Table1 with statement as a colum name, and I will like to create a column called location, where the script can search in the statement column and once it finds any of the items in the list in any row it states that in the location column

(Tema, london, Sydney, Germany, China, Africa,)

Statement 
-------------------
Going to london
Apples in Tema
Sydney is a city
China is a country
Africa is a continent

In the end I hope to see a table like this :

Statement location
Going to london London
Apples in Tema Tema
Sydney is a city Sydney
china is a country China
Africa is a continent Africa

By using this script,

SELECT Statement, 
Case
WHEN Statement::text ~~* '%london%'::character varying::text
    THEN 'london'::character varying
      ELSE NULL::character varying
        END AS location
FROM Table1

I think I would have to write a very tall script, but I was wondering if I could get help with something efficient and quite simple to achieve this

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you have a list of places, you can use that:

select t1.*, v.place
from table1 t1 cross join
     (values ('tema'), ('london'), ('sydney'), ('germany'), ('china'), ('africa')
     ) v(place)
     on Statement::text ilike '%' || v.place || '%';

Note: You might want to use regular expressions so you can include work boundaries but your example code doesn't do tis.

Upvotes: 1

Related Questions