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

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()

CodePudding user response：

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