Search code examples
hive

Find a value in column in hive

I have a requirement in hive to find a value in a column, data is ";" separated values in single column. if my required value present it should result TRUE else FALSE.

Ex: data in a single column is

Row1: insurance;finance;telecom

Row2: insurance, retail

Row3: finance, telecom, internal

Like this I have different values in a column. I wanted to find if the column contains "finance" or not. If present it should result TRUE else FALSE

output: Row1: TRUE

Row2: FALSE

Row3: TRUE

Please help. Thanks in advance.

Solution

  • If you are looking for single hardcode value, you can use like operator.
    where col like '%finance%'.

    But if you want it to be a variable qnd coming from another table you can use it like this

    Select...
From table1 t1,
join table2 t2 on t1.col like concat('%', t2.colpattern, '%')