I have the data with log-text as per the system recorded.

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    Key1 varchar(50)
    ,DateStamped date
    ,LogText nvarchar(max)
)

INSERT INTO #TblName VALUES ('PF1','2021-09-01','Gabriela.Santa 18:26:25  OnHold: False -> True  OnHoldDate:  -> 9/1/2021  OnHoldReasonCode:  -> NEWPDCN')
INSERT INTO #TblName VALUES ('PF1','2022-12-26','NhatCuong.Nguyen 08:21:18  OnHold: True -> False  OnHoldDate: 12/23/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   NhatCuong.Nguyen 15:10:42  OnHold: False -> True  OnHoldDate:  -> 12/26/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2023-01-05','NhatCuong.Nguyen 13:37:42  OnHold: True -> False  OnHoldDate: 12/26/2022 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:36:24  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG  Nguyen.Anh 14:55:42  OnHold: True -> False  OnHoldDate: 1/5/2023 ->   OnHoldReasonCode: NoCnf ->   OnHoldComment_c: 3D for Chemical miliing change, need to update CM BDWG ->   Quoc.Phan 14:57:29  OnHold: False -> True  OnHoldDate:  -> 1/5/2023  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2022-12-23','ThiThanh.Nguyen 10:12:22  AnalysisCode:  -> MachSO  Quoc.Phan 16:11:22  OnHold: False -> True  OnHoldDate:  -> 12/23/2022  OnHoldReasonCode:  -> NoCnf  OnHoldComment_c:  -> 3D for Chemical miliing change, need to update CM BDWG')
INSERT INTO #TblName VALUES ('PF1','2021-09-16','Valentin.Opris 12:08:52  OnHold: True -> False  OnHoldDate: 9/1/2021 ->   OnHoldReasonCode: NEWPDCN ->   Bogdan.Stefanescu 17:19:26  OnHold: False -> True  OnHoldDate:  -> 9/16/2021  OnHoldReasonCode:  -> NEWPDCN  Jothimani.Alagappan 17:29:53  ChangeRequestReason_c: CM_V0107 Spirit Sunshine - Move ST in house -> CM_V0330 SPIRIT SUNSHINE - Update Manufacturing Drawing PDCN review')

select * from #TblName

I am trying to search the latest user name from LogText column who have done the latest transaction in the system and the LogText contain OnHold: False -> True

So, the output from #TblName should be

CodePudding user response：

This is very bad design and you are going to have performance issues in the future. If the long text comes from log file or application you need to rewrite in order each event to be spillable correctly. For example, you can use:

• CHAR(29), CHAR(30), CHAR(31) - group, record and unit separator
• XML
• JSON

Then, when the data comes in your table you can use trigger or other routine to split the data and populate it in a normalized manner. For example, you need table for users, for keys,for on hold codes and for events. In the events table you will have:

• key id
• user id
• is on hold
• on hold date
• on hold reason code id

and you will be able to solve task like the ask one easier.

CodePudding user response：

To find the latest user name in the LogText column that has done a transaction in the system you can use the following query:

select t1.key1, t1.datestamped, substring(t1.logtext, charindex('onhold: false -> true', t1.logtext) - 20, 20) as username
from #tblname t1
inner join (
  select key1, max(datestamped) as max_date
  from #tblname
  where logtext like '%onhold: false -> true%'
  group by key1
) t2 on t1.key1 = t2.key1 and t1.datestamped = t2.max_date