CODEWITHSUNDEEP
sqlpostgresqlpattern-matching
baby_jee
baby_jee

Reputation: 53

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?

Upvotes: 5

Views: 9479

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

Assuming we are dealing with a comma-separated list of elements in the form [Location].[XX], I would expect this construct to perform best:

SELECT location
FROM   tbl
JOIN  (
   SELECT substring(unnest(string_to_array('[Location].[SG],[Location].[PH]'::text, ',')), 13, 2) AS location
   ) t USING (location);

Step-by-step

  • Transform the comma-separated list into an array and split it to a table with unnest(string_to_array()).
    You could do the same with regexp_split_to_table(). Slightly shorter but more expensive.

  • Extract the XX part with substring(). Very simple and fast.

  • JOIN to tbl instead of the IN expression. That's faster - and equivalent while there are no duplicates on either side.
    I assign the same column alias location to enable an equijoin with USING.

Upvotes: 2

mu is too short
mu is too short

Reputation: 434975

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.

Upvotes: 3

Akash
Akash

Reputation: 5012

Directly using location in ('something') works

I have create a fiddle that uses IN clause on a VARCHAR column

http://sqlfiddle.com/#!12/cdf915/1

Upvotes: 0

Related Questions