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

Question

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?

@galat based on your comment on the below answer you need to update this question. Please update with expected output, also note that you want to check each element of the test_column as an array and not as a string. Please note that the string is separated by ", " with a space rather than just columns — EoinS
– EoinS, Commented May 24, 2022 at 18:05

EoinS · Accepted Answer · 2022-05-24 18:07:07Z

1

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

edited May 24, 2022 at 18:07

answered May 24, 2022 at 17:33

EoinS

5,4901 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Galat Over a year ago

Hi, the second row is wrong. Need to be 'no'. The goal is to find an exact value, i.e. only 'L', but not 'L' as part of 'XL'.

EoinS Over a year ago

@galat that is not clear from your question. Please update

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related