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

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

Answer

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.+'

Advertisement

Answer