PostgreSQL 12 include variable in select statement

Question

I have an SQL query which look like this

SELECT t.test1 AS test01
FROM my_test_table t
WHERE t.test01 LIKE any('{data_nr0%,data_nr1%,data_a%}')

It works and everything is fine, but I don't know how to enhance it that in the output it would include extra pieces of data that are in ANY().

I need an output to be

test01	parameter_
data_nr0123	data_nr0
data_nr0122	data_nr0
data_nr0124	data_nr0
data_nr1123	data_nr1
data_nr1122	data_nr1
data_nr1124	data_nr1
data_a123	data_a

where parameter_ column has values that are inside ANY() operator.

UPDATE NO. 1

Tried

SELECT SUBSTRING(t.test1, 1, 8), t.test1 AS test01
FROM my_test_table t
WHERE t.test01 LIKE any('{data_nr0%,data_nr1%}')

but this works only if values in ANY operator are of the same length. How to make it work with variable length values?

Serg · Accepted Answer · 2021-04-29 16:01:48Z

1

Find a relevant match with CASE

SELECT case when t.test01 LIKE 'data_nr0%' then 'data_nr0'
            when t.test01 LIKE 'data_nr1%' then 'data_nr1' end param
, t.test1 AS test01
FROM my_test_table t
WHERE t.test01 LIKE any('{data_nr0%,data_nr1%}')

Alternatively you can have a parameters table / cte, then

-- sample data
with my_test_table(test01) as(
    select 'data_nr01' union all
    select 'data_nr13' 
),
params(val) as (
    select 'data_nr0' union all
    select 'data_nr1' 
)
SELECT t.test01, min(p.val)
FROM my_test_table t
JOIN params p on t.test01 like p.val||'%'
GROUP BY t.test01

edited Apr 29, 2021 at 16:01

answered Apr 29, 2021 at 15:45

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

1 Comment

miroana Over a year ago

Thank you for your response, what if there are many values inside ANY() (let's say 100 values), should I write them all in case... when... Or is there a better approach?

Collectives™ on Stack Overflow

PostgreSQL 12 include variable in select statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related