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

• frederico_fonseca

  SSCoach

  Points: 16212

  More actions

  March 28, 2023 at 7:35 am

  #4165842

  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