Find specific string in column and retrieve text that follows it

Question

Find specific string in column and retrieve text that follows it

fafful

SSC Eights!

Points: 810
More actions
March 28, 2023 at 6:30 am

#4165820

I have a table contains 2 fields , objid and COMMENT as below:
objid | COMMENT
1 | CHG_ASS_TIME;2023-05-02T08:15:00;2023-05-02T12:15:00#CHG_ASS_TEAM;Q829#CHG_FORCED
2 | NULL
3 | CHG_ASS_TIME;2022-03-02T08:15:00;2022-03-02T12:15:00#CHG_ASS_TEAM;W289
4 | CHG_STATUS;new#JOB_NEW
5 | CHG_ASS_TIME;2023-03-24T15:10:00;2023-03-24T17:28:00#ST_MOD;started;completed
I would like to have the COMMENT field for the table split in this way:
Header:
|objid|CHG_ASS_TIME_start|CHG_ASS_TIME_finish|CHG_ASS_TEAM|CHG_FORCED|ST_MOD started|ST_MOD_compl|
values:
|1 | 2023-05-02T08:15:00|2023-05-02T12:15:00|Q829|true|NULL|NULL|
|2 | NULL|NULL|NULL|NULL|NULL|NULL|
|3 | 2022-03-02T08:15:00|2022-03-02T12:15:00|W289|false|NULL|NULL|
|4 | NULL|NULL|NULL|false|NULL|NULL|
|5| 2023-03-24T15:10:00|2023-03-24T17:28:00|NULL|false|started|completed|
So when the COMMENT field contains "CHG_ASS_TIME" then the first text that follows the word CHG_ASS_TIME; is to be CHG_ASS_TIME_start and the next text after ; goes in CHG_ASS_TIME_finish and the delimeter for field with values id # . If "CHG_FORCED" is in comment then value is true else false. If "CHG_ASS_TEAM" is not found then value for field CHG_ASS_TEAM is NULL
Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16175 More actions · Answer 1

following "works" - but see comments

select t.objid
     --, t.COMMENT 
     , times.*
     , team.chg_ass_team
     , case
       when t.COMMENT is null
           then null
       when t.COMMENT like '%CHG_FORCED%'
           then 'true'
       else 'false'
       end as chg_forced
     , case
       when t.COMMENT like '%started%'
           then 'started'
       else null
       end as st_mod_started
     , case
       when t.COMMENT like '%completed%'
           then 'completed'
       else null
       end as st_mod_compl

from (values (1, 'CHG_ASS_TIME;2023-05-02T08:15:00;2023-05-02T12:15:00#CHG_ASS_TEAM;Q829#CHG_FORCED')
           , (2, null)
           , (3, 'CHG_ASS_TIME;2022-03-02T08:15:00;2022-03-02T12:15:00#CHG_ASS_TEAM;W289')
           , (4, 'CHG_STATUS;new#JOB_NEW')
           , (5, 'CHG_ASS_TIME;2023-03-24T15:10:00;2023-03-24T17:28:00#ST_MOD;started;completed')
     ) t (objid, comment)
outer apply (select substring(t.COMMENT, charindex('CHG_ASS_TIME', t.COMMENT) + 13, 19) as chg_ass_time_start
                  , substring(t.COMMENT, charindex('CHG_ASS_TIME', t.COMMENT) + 33, 19) as chg_ass_time_finish
             where comment like '%CHG_ASS_TIME%'
            ) times
-- if team size is not always 4 characters then further code is required to determine the end of the team value
outer apply (select substring(t.COMMENT, charindex('CHG_ASS_TEAM', t.COMMENT) + 13, 4) as chg_ass_team
             where comment like '%CHG_ASS_TEAM%'
            ) team