Search code examples
sqlimpala

Extract a value from a string and put it as calue in another column

I have some strings in a column in Impala like

'class:104;teacher:ted;school:first;subclass:404'
'class:105;teacher:nick;subclass:650;students:400'

I want to do a

case when info like '%class%' then substr(info,6,3) end as class
case when info like '%subclass%' then **<Here to take the value of the subclass>** end as subclass

How can I do it? As you can see it is not positional in order to go with substr()

Solution

  • I think you can use split_part() here.

    class - split_part(split_part(col, 'class:',2),';',1)
    subclass - split_part(split_part(col, 'subclass:',2),';',1)

    Inner split will split on class word and take second part('104;teacher:ted;school:first;subclass:404'). Then outermost split part will split on ; and pick up first part (104).

    Your SQL should be like -

    SELECT 
split_part(split_part(col, 'class:',2),';',1) as `class`,
split_part(split_part(col, 'subclass:',2),';',1) as `subclass`
FROM mytab