CODEWITHSUNDEEP
sqlhive
S M
S M

Reputation: 101

How to split values from one string column

I have HIVE table with string column, in one column it have numbers of values and I would like to split those values into columns. Here is sample column values.

enter image description here

End result should look like this enter image description here

I have used split function, since values in columns is not at same location , I am getting wrong values in columns, is there any function which takes value base on its tag value

Upvotes: 2

Views: 62

Answers (1)

notNull
notNull

Reputation: 31470

You need to use regexp_extract function and keep matching regular expression and extract the value.

Regular expression:

lanes=>"(.*?)" //literal match for lanes=>" and capture until next following occurance " 
                and keep it in first capture group

Example:

with cte as (--sample data
    select stack(2,string('lanes=>"2","txt_mid"=>"0"'),
                    string('"is_in"=>"parksville"'))as(c1)) 
    select regexp_extract(c1,'lanes=>"(.*?)"',1)lanes,
            regexp_extract(c1,'"txt_mid"=>"(.*?)"',1)txt_mid 
        from cte;

Result:

lanes   txt_mid
2       0

In case if you want to null values for missing data records, then use case-when-then statement to check length of the field if 0 then have null value for the record.

Upvotes: 1

Related Questions