PostgreSQL: How to check if the value is in the list of values within the text-CodePudding

I have a single value in one column and a string of values in another column, so how it is possible to check if a single value is in the list of values? The goal is to find an exact value, i.e. only 'L', but not 'L' as part of 'XL'

with cte as
(
select 'L' as test_char, 'S, M, L, XL, XXL, 3XL' as test_column
union
select 'L', '30, 40, 50, 60, 70'
union
select 'L', '30L, 40X, 50M, 60XL, 70XXL'
)   
select test_char, 
       test_column,
       case when test_char in (test_column) then 'yes' else 'no' end as check_column
from cte

I try to check with in but can not find it. (in the third row the check_column need to be 'yes').

it is possible to convert values in strings to an array and to check each value in the array? Or any other ideas?

CodePudding user response：

It seems like you want to treat your string like an array and look at each member. Looks like your test_column is separated by `", " so you could use the below

, string_to_array(test_column,', ') @> array['L']

From docs

Operator    Description Example                     Result
@>          contains    ARRAY[1,4,3] @> ARRAY[3,1]  t

Docs