Search code examples
sqlstringhive

HIVE SQL: Select rows whose values contain string in a column

I want to select rows whose values contain a string in a column.
For example, I want to select all rows whose values contain a string '123' in the column 'app'.
table:

app         id
123helper   xdas
323helper   fafd
2123helper  dsaa
3123helper  fafd
md5321      asdx
md5123      dsad

result:

app         id
123helper   xdas
2123helper  dsaa
3123helper  fafd
md5123      dsad

I am not familiar with SQL query.
Could anyone help me? .
Thanks in advances.

Solution

  • In a number of ways:

    like:

    select * from table
where app like '%123%'

    rlike:

    ...
where app rlike '123'

    instr:

    ...
where instr(app, '123')>0

    locate:

    ...
where  locate('123', app)>0

    Invent your own way.

    Read manual: String Functions and Operators.