I have a table x with column called Sprint that looks like below(note that I am just showing one row from this table as an example):

I need help writing SQL to extract name, startDate and endDate as separate columns in a view where the output would look like as listed below;

CodePudding user response：

you can try using REGEXP_INSTR as below done for a name column.

select column1,
REGEXP_INSTR(column1, 'name=') name_start,
REGEXP_INSTR(column1, ',',REGEXP_INSTR(column1, 'name=') ) name_end,
substr(column1,name_start,name_end-name_start)
from values
('com.atlassian.greenhopper.service.sprint.Sprint@55efff25[id=4659,rapidViewId=422,state=CLOSED,name=Sprint 108,startDate=2020-12-22T21:48:00.000-06:00,endDate=2021-01-04T21:48:00.000-06:00,completeDate=2021-01-05T01:28:18.156-06:00,activatedDate=2020-12-23T03:01:14.201-06:00,sequence=4658,goal=,autoStartStop=false]');

CodePudding user response：

SELECT SPLIT_PART(SPRINT, ',', 4) as Name,
SPLIT_PART(SPRINT, ',', 5) as StartDate,
SPLIT_PART(SPRINT, ',', 6) as EndDate,
FROM x;

I tried this and it worked Just need to clean the column and change data type.