CODEWITHSUNDEEP
sqlpostgresql
miroana
miroana

Reputation: 552

PostgreSQL 12 include variable in select statement

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?

Upvotes: 0

Views: 107

Answers (1)

Serg
Serg

Reputation: 22811

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

Upvotes: 1

Related Questions