how to use where like IN in impala-CodePudding

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%')

CodePudding user response：

Have you considered using STRLEFT?

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

CodePudding user response：

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.