March 28, 2023 at 6:30 am
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
March 28, 2023 at 7:35 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy