regex - search for specific characters within column and then create different columns from it -

i have param_value column have different values. need extract these values , create columns of them.

  |param_name |param_value |     __________|____________   |step 4     |  sp:0.09   |   |procedure  |  max:125   |   |step 4     |  sp:ambient|   |(null)     |  +/-:n/a   |   |steam      |  sp:2      |   |step 3     |  min:0     |   |step 4     |  rdphn427b |   |testing de |  n/a       | 

i want columns with: , give them names:

  sp:                set_point_value,   max:               max_limit,   min:               min_limit,   +/-:               upper_lower_limit 

so have far is:

create or replace force view process_steps  ("param_name", "set_point_value", "upper_lower_limit", "max_value", "min_value") select param_name,                regexp_like("param_value", 'sp:')   set_point_value,        regexp_like("param_value", '+/-:') upper_lower_limit,        regexp_like("param_value", 'max:')  max_value,        regexp_like("param_value", 'min:')  min_value process_steps  ; 

i'm more familiar tsql , mysql, ought think you're looking for. if doesn't exactly, should @ least point in right direction.

create or replace force view process_steps      ("param_name", "set_point_value", "upper_lower_limit", "max_value", "min_value") select param_name           , case when "param_value" 'sp:%'             substr("param_value", instr("param_value", ':')+1)             else null         end set_point_value         , case when "param_value" '+/-:%'             substr("param_value", instr("param_value", ':')+1)             else null         end upper_lower_limit         , case when "param_value" 'max:%'             substr("param_value", instr("param_value", ':')+1)             else null         end max_value         , case when "param_value" 'min:%'             substr("param_value", instr("param_value", ':')+1)             else null         end min_value     process_steps ; 

the basic concept here identifying information want via like, using substr , instr extract it. while stay away from, since there's no leading % in case, it's sargable, , not total efficiency sink.

really, though, have ask question why you're laying out data - substring operations slow in language, , db no exception. why not use column limit type? why not lay out in view you're looking at?