CODEWITHSUNDEEP
sqlstringhive
Bowen Peng
Bowen Peng

Reputation: 1815

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.

Upvotes: 2

Views: 6488

Answers (3)

leftjoin
leftjoin

Reputation: 38290

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.

Upvotes: 2

Jim Macaulay
Jim Macaulay

Reputation: 5141

Please use below query, 

select app, id from table where app like '%123%';

Below are few additional information,

like '123%' --> Starts with 123
like '%123' --> Ends with 123
like '%123%'--> Contains 123 anywhere in the string

Upvotes: 2

zealous
zealous

Reputation: 7503

Try the following using like

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

Output:

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

Upvotes: 2

Related Questions