how to use where + like IN in impala

Question

I would like to use like in a where statement using a list like test:

select * 
FROM mydb.my_table.K
WHERE K.myid LIKE IN ('AT%', 'BEL%' , 'DDCY%')

This doees not work, while this does:

select * 
FROM mydb.my_table.K
WHERE (K.myid LIKE 'AT%' OR K.myid LIKE'BEL%' OR K.myid LIKE 'DDCY%')

score 1 · Answer 1 · answered Dec 20 '21 at 11:01

1

Have you considered using STRLEFT?

select * 
FROM mydb.my_table.K
WHERE STRLEFT(K.myid,2) IN ('AT', 'BE' , 'CY')

answered Dec 20 '21 at 11:01

Paul Alan Taylor

10,474
1
26
42

nice work arround, yet it might no generalize well to more complicated string patterns, so I will leave the question up for bit – safex Dec 20 '21 at 11:17
Neither is a LIKE. FWIW, I don't think syntax exists to allow you to do what you want to do. LIKE is for comparing strings. IN is for checking existence in a set. If you're hellbent on using LIKE you're going to need to dynamically build that WHERE clause. The only issues with the solution enlisted above is that right now, it only does the first two characters. Your never-mentioned-before "more complicated string patterns" would be additional OR clauses. https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql – Paul Alan Taylor Dec 20 '21 at 12:09
i do not this this will work ` 'CY'` to look for a pattern `DDCY%`. – Koushik Roy Dec 20 '21 at 13:28

score 0 · Answer 2 · answered Dec 20 '21 at 13:26

Your first SQL is syntactically correct but it will not work as per logic. Second SQL does the trick but its slow and if you have many arguments this can be a problem.

Unfortunately you can not use arguments in a list but you can use rlike or regexp_like to do the trick.

select * 
FROM mydb.my_table.K
WHERE K.myid RLIKE '^AT|^BEL|^DDC'

Please note this is a regular expression and ^ means start of a string and | concatenates all pattern arguments.

how to use where + like IN in impala

2 Answers2