Search postgres in column separated by regexp_split_to_table

Question

I have the following table:

cs_id ; cs_values ; cs_desc --------------------------- 1; 'a,b,c'; 'one' 2; 'd,a'; 'two' 3; 'a,c'; 'three' 

The cs_valies field contains various values, separated by commas. I would like to get all the "cs_id" in strings that contain a specific value in the "cs_values".

I used this expression:

 SELECT cs_id, regexp_split_to_table(cs_values, '* , *') as splitted_value WHERE splitted_value = 'a' 

I have some questions:

• Postgres does not like alias names in a WHERE -clause. Or does anyone have an idea how to achieve this?
• Does anyone have a better solution to solve this problem?

Thanks to everyone, I hope that I do not miss something extremely obvious.

Answer 1

Postgres does not like aliases in a WHERE clause. Or does anyone have an idea how to achieve this?

 SELECT * FROM ( SELECT cs_id, regexp_split_to_table(cs_values, '* , *') as splitted_value ) t WHERE splitted_value = 'a' 

Does anyone have a better solution to solve this problem?

Normalize the table and put the "comma list" in your table. This is a classic 1: n ratio

Answer 2

I had a similar situation on my hands. Here, what I found works with pgsql:

 select a.id, a.name from student as a, (select id, cast(regexp_split_to_table(topics, ',') as text) as topic from student) as b where a.id = b.id and b.topic = 'science'; 

Hope this helps too.