Hive - find 2 characters anywhere in the string/row - RLIKE

Question

How do I get the data for ONLY “_WA” data assigned to "USA_RBB_WA_BU"? However the column I look at has rows that contain _WA and _SA (USA_CA_SAWANT)

I used,

                        select....
                         'USA_RBB_WA_BU' AS State ,
                         District 
                         from TABLE where district like '%_WA%'


                        select   ...
                         'USA_RBB_NSW_BU' AS State ,
                         District 
                         from TABLE where district like '%_SA%'

however I ended up with data output table below

**state                  district**
USA_RBB_NSW_BU          USA_CA_SAWANT
USA_RBB_WA_BU           USA_CA_SAWANT
USA_RBB_NSW_BU          USA_CA_SAWANT
USA_RBB_WA_BU           USA_CA_SAWANT

I tried

                        select....
                         'USA_RBB_WA_BU' AS State ,
                         District 
                         from TABLE where district like '_WA%'

                         output was  was 0 results in hive

score 1 · Accepted Answer · answered Jan 30 '20 at 06:36

1

where district rlike '_WA' will work fine (this will return TRUE if string contains _WA)

If you want string to contain something before and after _WA, add .+ (any character one or more times):

where district rlike '.+_WA.+'

answered Jan 30 '20 at 06:36

leftjoin

36,950
8
57
116

Thanks for the reply. However,The output was 0 results for this. I am looking for is "_WA" anywhere in the string. When matching it should NOT take just "WA" instead match exactly "_WA" My code above finds "WA" its wrong because "USA_CA_SAWANT" should be part of "from TABLE where district like '%_SA%'". thanks for your input – YJG Jan 30 '20 at 23:06
@YJG `rlike '_WA'` works exactly according to your requirement - "_WA" anywhere in the string. – leftjoin Jan 31 '20 at 04:55
Thanks for the response and Yes = where district rlike '_WA' works exactly as intended. My bad I only noticed the where district rlike '.+_WA.+' on your answer before. Thanks again – YJG Feb 02 '20 at 22:36

score 0 · Answer 2 · answered Jan 30 '20 at 05:43

0

I guess you are looking for escaping _ here. Try below -

SELECT <FIELD_LIST>
       'USA_RBB_WA_BU' AS State ,
       District 
FROM TABLE
WHERE district RLIKE '.*_WA%'

answered Jan 30 '20 at 05:43

Ankit Bajpai

13,128
4
25
40

Thanks for the reply Ankit. However,The output was 0 results for this like leftjoin . I am looking for is "_WA" anywhere in the string. When matching it should NOT take just "WA" instead match exactly "_WA" My code above finds "WA" its wrong because "USA_CA_SAWANT" should be part of "from TABLE where district like '%_SA%'". thanks for your input – YJG Jan 30 '20 at 23:07

Hive - find 2 characters anywhere in the string/row - RLIKE

2 Answers2