I have a string column in redshift table like:

String
["0","0","5","0","0","0","0","0","0","0","0","0","0","0","0","0","0"]
["0","0","0","8","30","0","0","0","0"]
["0","0"]

I want to split the string column to get the first 6 characters of the string in 6 different columns as shown below

I am new to Redshift and regex concepts. Any help would be of great help.

CodePudding user response：

Try REGEXP_SUBSTR:

select 
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 1) as col1,
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 2) as col2,
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 3) as col3,
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 4) as col4,
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 5) as col5,
  REGEXP_SUBSTR(mystr, '[0-9] ', 1, 6) as col6
from mytable;