Text to List in SQL

Is there any way on how to convert a comma separated text value to a list so that I can use it with ‘IN’ in SQL? I used PostgreSQL for this one.

Ex.:

select location from tbl where 
location in (replace(replace(replace('[Location].[SG],[Location].[PH]', ',[Location].[', ','''), '[Location].[', ''''), ']',''''))

This query:

select (replace(replace(replace('[Location].[SG],[Location].[PH]', ',[Location].[', ','''), '[Location].[', ''''), ']',''''))

produces ‘SG’,’PH’

I wanted to produce this query:

select location from tbl where location in ('SG','PH')

Nothing returned when I executed the first query. The table has been filled with location values ‘SG’ and ‘PH’.

Can anyone help me on how to make this work without using PL/pgSQL?

Answer

So you’re faced with a friendly and easy to use tool that won’t let you get any work done, I feel your pain.

A slight modification of what you have combined with string_to_array should be able to get the job done.

First we’ll replace your nested replace calls with slightly nicer replace calls:

=> select replace(replace(replace('[Location].[SG],[Location].[PH]', '[Location].', ''), '[', ''), ']', '');
 replace 
---------
 SG,PH

So we strip out the [Location]. noise and then strip out the leftover brackets to get a comma delimited list of the two-character location codes you’re after. There are other ways to get the SG,PH using PostgreSQL’s other string and regex functions but replace(replace(replace(... will do fine for strings with your specific structure.

Then we can split that CSV into an array using string_to_array:

=> select string_to_array(replace(replace(replace('[Location].[SG],[Location].[PH]', '[Location].', ''), '[', ''), ']', ''), ',');
 string_to_array 
-----------------
 {SG,PH}

to give us an array of location codes. Now that we have an array, we can use = ANY instead of IN to look inside an array:

=> select 'SG' = any (string_to_array(replace(replace(replace('[Location].[SG],[Location].[PH]', '[Location].', ''), '[', ''), ']', ''), ','));
 ?column? 
----------
 t

That t is a boolean TRUE BTW; if you said 'XX' = any (...) you’d get an f (i.e. FALSE) instead.

Putting all that together gives you a final query structured like this:

select location
from tbl
where location = any (string_to_array(...))

You can fill in the ... with the nested replace nastiness on your own.