dcsimg
Results 1 to 17 of 17

Thread: An interesting query based on a date buried in a big long string

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    An interesting query based on a date buried in a big long string

    Hello. I hope everyone is doing well with their social distancing and being safe and healthy!

    I have to write a query based on a date range but the date is in a "note".
    The table is called Jobs
    Within the Jobs table is a field called Details, it is defined as ntext.
    Here is an example of what can be in Details:

    employee 22 on 03/25/20 14:31:CP All
    System on 03/25/20 14:31:Case Closed by Sync P21 System Function
    employee 85 on 03/24/20 11:13:Ready to Invoice
    employee 85 on 03/24/20 09:24:Waiting Shipping
    employee 85 on 03/24/20 09:24:Order Received
    employee 19 on 03/17/20 16:36:On Order - xxx Parts
    employee 19 on 03/17/20 16:36:Case was modified using Convert Case to Order function
    employee 24 On 03/06/20 10:27> PO added to case folder. This was in P21, but no CMS case. Please updated as needed. Thanks


    What I am interested in is the line that says Case Closed by Sync P21 System Function. I need to grab 03/25/20. And then use it in a date range criterion, for example where that date is > 1/1/20 and less than getdate().

    I will try to get started but I am thinking this will take me a while and I may not be doing it the best way so if you can help, thank you.
    Last edited by MMock; Mar 26th, 2020 at 11:18 AM. Reason: Accidentally hit enter!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    Here goes. And it seems to work. If you care to look, do you see any issues with it?
    Code:
     select 
      J.Details
    , CHARINDEX('Case Closed by Sync P21 System Function', j.Details) AS idxSync
    -- 63 Then I have to back up by 15 characters to get to 03/11/20 and grab those 8 characters
    , SUBSTRING(j.Details, CHARINDEX('Case Closed by Sync P21 System Function', j.Details) - 15, 8) AS dateSync
    from jobs J																
    WHERE bStateContract = 1
    AND CHARINDEX('Case Closed by Sync P21 System Function', j.Details) > 0
    AND SUBSTRING(j.Details, CHARINDEX('Case Closed by Sync P21 System Function', j.Details) - 15, 8) > '01/01/2020'
    AND SUBSTRING(j.Details, CHARINDEX('Case Closed by Sync P21 System Function', j.Details) - 15, 8) < GETDATE()
    order by j.control
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,391

    Re: An interesting query based on a date buried in a big long string

    Can you use string split..... string_split(bigstring,' ')
    then look for date
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    I can't just look for "a date"; I need the date specifically on the line with the Sync P21 keywords.
    Are you suggesting that because you see an issue with my solution?
    Also, string_split is not available in my version, 12.0 SP2.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,113

    Re: An interesting query based on a date buried in a big long string

    Uhh.... why don't you just use a WHERE Details LIKE '%Case Closed by Sync P21%' to filter out everything you DON'T need?
    If those entries always have the same structure ("System on SomeDate") it's simple string-parsing from the left.
    If it's MS SQL-Server you might take a look at the PATINDEX-Function to grab your date.

    As for your query: I'd be very, very careful comparing strings with dates (Those SUBSTRING against GETDATE()). It works, someone changes his/her locale, BOOM

    As for social distancing....well....
    This dude is like me in the morning going to work in "home-office"
    Name:  home.jpg
Views: 31
Size:  54.9 KB
    Last edited by Zvoni; Mar 26th, 2020 at 04:50 PM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    LOL. When my daughter used to be in college (two weeks ago) she was a German Studies major! She translated for me
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    OK, getting back to your reply to my actual problem...there is no structure. There are CR/LF's stored in the big long Details ntext column which is why it pasted as individual lines. So I can't parse from the left; the entire string goes from left and on and on eventually ending to the right.

    Can you say more about a changing locale?

    What could be possible, but a lot of work, would be when the users are in the form where the P21 Sync note gets generated I could from now on write the date to a new column in the table. Then, write a one-shot that would go through all existing records and populate the column from the date in Details. That seemed like a lot of work and scary, but maybe in the end it would be more reliable?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,113

    Re: An interesting query based on a date buried in a big long string

    Ah crap.... CrLf's inside the text......

    What DBMS is that? MS SQL?
    I just saw a 12.0 SP2, but can't correlate it to any DBMS except maybe PostGres
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    Sorry, sql server!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,498

    Re: An interesting query based on a date buried in a big long string

    To be honest, there's dozens of ways of skinning this but none of them are going to be significantly better than what you've done. Some, like string splitting on CrLf might end up being a bit more readable but they're all going to come back to the same basic principle: Look for the string pattern match, look for the date that precedes it.

    The problem you're really facing is that they're all likely to be flakey because you're trying to infer structure from an unstructured text field. This data should not be being blobbed into a single text field like that. Is there any scope for a redesign to separate those records out into separate records with (for example) User, Date and Event fields?
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,130

    Re: An interesting query based on a date buried in a big long string

    Quote Originally Posted by FunkyDexter View Post
    Is there any scope for a redesign to separate those records out into separate records with (for example) User, Date and Event fields?
    There's always scope for a redesign. I have not been a fan of this current design from the beginning. You will probably advise me to do that now to tackle any future problems (like if I ever need one of the other dates) but what did you think about my idea in #7, last paragraph?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  12. #12
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,391

    Re: An interesting query based on a date buried in a big long string

    Dumping that all into a single blob string is difficult to work with (believe me I know) here we are using a product from solarwinds named syslogger
    it dumps log files into a table in one field... this is what I have to do to get the data out in a usable format (I actually store the out put to a table)
    Code:
    DECLARE @start DATETIME = '2/26/2019 14:32:50', --DATEADD(HOUR,-1,GETDATE())
    		@end DATETIME = GETDATE(), --GETDATE(),--'2/26/2019 14:33:52',
    		@themessage VARCHAR(max);
    
    WITH ctePullMe AS (
    SELECT 
    		CAST(SL.Datetime AS DATE) AS DATERUN
    		,DATEPART(HOUR,SL.datetime) AS HourCollected
    		,DATEPART(MINUTE,SL.datetime) AS MinuteColleted
    		,DATEPART(SECOND,SL.DateTime) AS SecCollected
    		,SL.Message
    		,SUBSTRING(SL.message,(CHARINDEX('"Total":',SL.message) + 8), 50)   As TotalTiming
    		,SUBSTRING(SL.message,(CHARINDEX('"CMP":',SL.message) + 6), 500) AS ComponentTotals
    		,SUBSTRING(SL.message,(CHARINDEX(',"DB":',SL.message) ),300) AS DBComponents
    		,SUBSTRING(SL.message, CHARINDEX(',"RS":',SL.message), 300) AS RSComponents
    		,SUBSTRING(SL.message, CHARINDEX(',"MCM":',SL.message), 300) AS MCMComponents
    		,SUBSTRING(SL.message, CHARINDEX(',"HSM":',SL.message), 300) AS HSMComponents
    		,CASE 
    			WHEN CHARINDEX(',"SecureKey":',SL.message) > 0 THEN SUBSTRING(SL.message, CHARINDEX(',"SecureKey":',SL.message), 300)
    			ELSE NULL
    		 END AS SecureKeyComponents
    		,SUBSTRING(SL.message, CHARINDEX(',"INT":',SL.message), 300) AS INTComponents
    		,SUBSTRING(SL.message, CHARINDEX('Host Timing',SL.message), 300) AS HostTimingComponents
    		,SUBSTRING(SL.message, CHARINDEX('RDHSTransRouter,I,[',SL.message), CHARINDEX('Router Timing:',SL.[message])-  CHARINDEX('RDHSTransRouter,I,[',SL.message) ) AS HeaderInfo
    	
    	FROM #tempTest SL WITH (NOLOCK)
    	WHERE datetime BETWEEN @start AND @end
    			AND ip IN (
    						--Mass Data Center IPs
    						'XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX'
    						--NY Data Center IPs
    						,'XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX','XXX.XXX.XXX.XXX'
    					  )
    			AND CHARINDEX('Router Timing:',message) > 0
    )
    
    
    SELECT 
    		DATERUN
    		,HourCollected
    		,MinuteColleted
    		,c1.SecCollected
    --		TotalTime
    		,CAST(SUBSTRING(TotalTiming,CHARINDEX(':',TotalTiming) + 1,( CHARINDEX('}',TotalTiming) - 2 - CHARINDEX(':',TotalTiming) + 1 )) AS DECIMAL(20,5)) AS TotalTransactionTime
    --		--Components
    		,CAST(SUBSTRING(ComponentTotals,CHARINDEX(':',ComponentTotals) + 1,( CHARINDEX('}',ComponentTotals) - 2 - CHARINDEX(':',ComponentTotals) + 1 )) AS DECIMAL(20,5)) AS DatabaseTime
    		,CAST(SUBSTRING(c1.ComponentTotals,CHARINDEX('{"RS":',c1.ComponentTotals) + LEN('"RS":')+ 1,(CHARINDEX('},',c1.ComponentTotals,CHARINDEX('{"RS":',c1.ComponentTotals)) - CHARINDEX('{"RS":',c1.ComponentTotals)  )- LEN('},') -4) AS DECIMAL(20,5)) AS RedisTime
    		,CASE CHARINDEX('"MCM":[]',ComponentTotals) 
    			WHEN 0 THEN (CAST(LEFT(SUBSTRING(ComponentTotals,(CHARINDEX('"MCM":',ComponentTotals) + 6),15),CHARINDEX('}',SUBSTRING(ComponentTotals,(CHARINDEX('"MCM":',ComponentTotals) + 6),15)) -1) AS DECIMAL(20,5))) 
    			ELSE NULL
    		 END AS MCMTime
    		,CAST(SUBSTRING(c1.ComponentTotals,CHARINDEX('{"HSM":',c1.ComponentTotals) + LEN('"HSM":')+ 1,(CHARINDEX('},',c1.ComponentTotals,CHARINDEX('{"HSM":',c1.ComponentTotals)) - CHARINDEX('{"HSM":',c1.ComponentTotals)  )- LEN('},') -5) AS DECIMAL(20,5)) AS HSMTime
    		,CAST(SUBSTRING(c1.ComponentTotals,CHARINDEX('{"HST":',c1.ComponentTotals) + LEN('"HST":')+ 1,(CHARINDEX('},',c1.ComponentTotals,CHARINDEX('{"HST":',c1.ComponentTotals)) - CHARINDEX('{"HST":',c1.ComponentTotals)  )- LEN('},') -5) AS DECIMAL(20,5)) AS HSTTime
    		,CAST(SUBSTRING(c1.ComponentTotals,CHARINDEX('{"INT":',c1.ComponentTotals) + LEN('"INT":')+ 1,(CHARINDEX('}],',c1.ComponentTotals,CHARINDEX('{"INT":',c1.ComponentTotals)) - CHARINDEX('{"INT":',c1.ComponentTotals)  )- LEN('}]') -5) AS DECIMAL(20,5)) AS INTTime
    
    		--DB Components
    		--,c1.DBComponents
    		,CASE 
    			WHEN CHARINDEX('ChkTxnID',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"ChkTxnId":',c1.DBComponents) + LEN('{"ChkTxnId":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"ChkTxnId":',c1.DBComponents)) - (CHARINDEX('{"ChkTxnId":',c1.DBComponents) +LEN('{"ChkTxnId":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_ChkTxnId
    		,CASE
    			WHEN CHARINDEX('{"NxtSeqNum":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"NxtSeqNum":',c1.DBComponents) + LEN('{"NxtSeqNum":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"NxtSeqNum":',c1.DBComponents)) - (CHARINDEX('{"NxtSeqNum":',c1.DBComponents) +LEN('{"NxtSeqNum":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_NxtSeqNum
    		,CASE
    			WHEN CHARINDEX('{"GetOrigTran":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"GetOrigTran":',c1.DBComponents) + LEN('{"GetOrigTran":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"GetOrigTran":',c1.DBComponents)) - (CHARINDEX('{"GetOrigTran":',c1.DBComponents) +LEN('{"GetOrigTran":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_GetOrigTran
    		,CASE
    			WHEN CHARINDEX('{"GetOrigRsp":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"GetOrigRsp":',c1.DBComponents) + LEN('{"GetOrigRsp":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"GetOrigRsp":',c1.DBComponents)) - (CHARINDEX('{"GetOrigRsp":',c1.DBComponents) +LEN('{"GetOrigRsp":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_GetOrigRsp
    		,CASE 
    			WHEN CHARINDEX('{"TxnIns":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"TxnIns":',c1.DBComponents) + LEN('{"TxnIns":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"TxnIns":',c1.DBComponents)) - (CHARINDEX('{"TxnIns":',c1.DBComponents) +LEN('{"TxnIns":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_TxnIns
    		,CASE 
    			WHEN CHARINDEX('{"BatchUpd":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"BatchUpd":',c1.DBComponents) + LEN('{"BatchUpd":'),CHARINDEX('},{',c1.DBComponents,CHARINDEX('{"BatchUpd":',c1.DBComponents)) - (CHARINDEX('{"BatchUpd":',c1.DBComponents) +LEN('{"BatchUpd":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_BatchUpd
    		,CASE 
    			WHEN CHARINDEX('{"TxnUpd":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"TxnUpd":',c1.DBComponents) + LEN('{"TxnUpd":'),CHARINDEX('}',c1.DBComponents,CHARINDEX('{"TxnUpd":',c1.DBComponents)) - (CHARINDEX('{"TxnUpd":',c1.DBComponents) +LEN('{"TxnUpd":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_TxnUpd
    		,CASE 
    			WHEN CHARINDEX('{"ReaderAudit":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"ReaderAudit":',c1.DBComponents) + LEN('{"ReaderAudit":'),CHARINDEX('}',c1.DBComponents,CHARINDEX('{"ReaderAudit":',c1.DBComponents)) - (CHARINDEX('{"ReaderAudit":',c1.DBComponents) +LEN('{"ReaderAudit":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_ReaderAudit
    		,CASE 
    			WHEN CHARINDEX('{"GetSecureKey":',c1.DBComponents) > 0 THEN CAST(SUBSTRING(c1.DBComponents,CHARINDEX('{"GetSecureKey":',c1.DBComponents) + LEN('{"GetSecureKey":'),CHARINDEX('}',c1.DBComponents,CHARINDEX('{"GetSecureKey":',c1.DBComponents)) - (CHARINDEX('{"GetSecureKey":',c1.DBComponents) +LEN('{"GetSecureKey":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS DB_GetSecureKey
    		
    		--RS Components
    		--,c1.RSComponents
    		,CASE 
    			WHEN CHARINDEX('{"Connection":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"Connection":',c1.RSComponents) + LEN('{"Connection":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"Connection":',c1.RSComponents)) - (CHARINDEX('{"Connection":',c1.RSComponents) +LEN('{"Connection":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_Connection
    		,CASE 
    			WHEN CHARINDEX('{"GetSession":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetSession":',c1.RSComponents) + LEN('{"GetSession":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetSession":',c1.RSComponents)) - (CHARINDEX('{"GetSession":',c1.RSComponents) +LEN('{"GetSession":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_GetSession
    		,CASE 
    			WHEN CHARINDEX('{"GetSessionUser":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetSessionUser":',c1.RSComponents) + LEN('{"GetSessionUser":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetSessionUser":',c1.RSComponents)) - (CHARINDEX('{"GetSessionUser":',c1.RSComponents) +LEN('{"GetSessionUser":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_GetSessionUser
    		,CASE 
    			WHEN CHARINDEX('{"GetTargetedUser":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetTargetedUser":',c1.RSComponents) + LEN('{"GetTargetedUser":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetTargetedUser":',c1.RSComponents)) - (CHARINDEX('{"GetTargetedUser":',c1.RSComponents) +LEN('{"GetTargetedUser":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_GetTargetedUser
    		,CASE 
    			WHEN CHARINDEX('{"GetTargetedAccess":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetTargetedAccess":',c1.RSComponents) + LEN('{"GetTargetedAccess":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetTargetedAccess":',c1.RSComponents)) - (CHARINDEX('{"GetTargetedAccess":',c1.RSComponents) +LEN('{"GetTargetedAccess":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_GetTargetedAccess
    
    		,CASE 
    			WHEN CHARINDEX('{"GetTokenization":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetTokenization":',c1.RSComponents) + LEN('{"GetTokenization":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetTokenization":',c1.RSComponents)) - (CHARINDEX('{"GetTokenization":',c1.RSComponents) +LEN('{"GetTokenization":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_GetTokenization
    		,CASE 
    			WHEN CHARINDEX('{"GetProcessor":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"GetProcessor":',c1.RSComponents) + LEN('{"GetProcessor":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"GetProcessor":',c1.RSComponents)) - (CHARINDEX('{"GetProcessor":',c1.RSComponents) +LEN('{"GetProcessor":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS GetProcessor
    		,CASE 
    			WHEN CHARINDEX('{"Cache":',c1.RSComponents) > 0 THEN SUBSTRING(c1.RSComponents,CHARINDEX('{"Cache":',c1.RSComponents) + LEN('{"Cache":') + 1,CHARINDEX('}',c1.RSComponents,CHARINDEX('{"Cache":',c1.RSComponents)) - (CHARINDEX('{"Cache":',c1.RSComponents) +LEN('{"Cache":') +2)) 
    			ELSE NULL
    		 END AS RS_Cache
    		,CASE 
    			WHEN CHARINDEX('{"Ping":',c1.RSComponents) > 0 THEN CAST(SUBSTRING(c1.RSComponents,CHARINDEX('{"Ping":',c1.RSComponents) + LEN('{"Ping":'),CHARINDEX('}',c1.RSComponents,CHARINDEX('{"Ping":',c1.RSComponents)) - (CHARINDEX('{"Ping":',c1.RSComponents) +LEN('{"Ping":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS RS_Ping
    
    		 --MCM Components
    		 --,c1.MCMComponents
    		 ,CASE 
    			WHEN CHARINDEX('{"ValUsr":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"ValUsr":',c1.MCMComponents) + LEN('{"ValUsr":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"ValUsr":',c1.MCMComponents)) - (CHARINDEX('{"ValUsr":',c1.MCMComponents) +LEN('{"ValUsr":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS MCM_ValUsr
    		,CASE 
    			WHEN CHARINDEX('{"RskChk":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"RskChk":',c1.MCMComponents) + LEN('{"RskChk":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"RskChk":',c1.MCMComponents)) - (CHARINDEX('{"RskChk":',c1.MCMComponents) +LEN('{"RskChk":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS MCM_RskChk
    		,CASE 
    			WHEN CHARINDEX('{"Report":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"Report":',c1.MCMComponents) + LEN('{"Report":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"Report":',c1.MCMComponents)) - (CHARINDEX('{"Report":',c1.MCMComponents) +LEN('{"Report":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS MCM_Report
    		,CASE 
    			WHEN CHARINDEX('{"CardListCheck":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"CardListCheck":',c1.MCMComponents) + LEN('{"CardListCheck":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"CardListCheck":',c1.MCMComponents)) - (CHARINDEX('{"CardListCheck":',c1.MCMComponents) +LEN('{"CardListCheck":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS MCM_CardListCheck
    
    		 --HSM Components
    		 --,c1.HSMComponents
    		 ,CASE 
    			WHEN CHARINDEX('{"Connect":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"Connect":',c1.MCMComponents) + LEN('{"Connect":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"Connect":',c1.MCMComponents)) - (CHARINDEX('{"Connect":',c1.MCMComponents) +LEN('{"Connect":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS HSM_Connect
    		,CASE 
    			WHEN CHARINDEX('{"Transmit":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"Transmit":',c1.MCMComponents) + LEN('{"Transmit":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"Transmit":',c1.MCMComponents)) - (CHARINDEX('{"Transmit":',c1.MCMComponents) +LEN('{"Transmit":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS HSM_Transmit
    		,CASE 
    			WHEN CHARINDEX('{"Receive":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"Receive":',c1.MCMComponents) + LEN('{"Receive":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"Receive":',c1.MCMComponents)) - (CHARINDEX('{"Receive":',c1.MCMComponents) +LEN('{"Receive":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS HSM_Receive
    		,CASE 
    			WHEN CHARINDEX('{"DcryptSecCrd":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"DcryptSecCrd":',c1.MCMComponents) + LEN('{"DcryptSecCrd":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"DcryptSecCrd":',c1.MCMComponents)) - (CHARINDEX('{"DcryptSecCrd":',c1.MCMComponents) +LEN('{"DcryptSecCrd":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS HSM_DcryptSecCrd
    
    		 --SecureKey Components
    		 --,c1.SecureKeyComponents
    		 ,CASE 
    			WHEN (c1.SecureKeyComponents IS NULL) THEN NULL	
    			ELSE CAST(SUBSTRING(c1.SecureKeyComponents,CHARINDEX('{"Create":',c1.SecureKeyComponents) + LEN('{"Create":'),CHARINDEX('}',c1.SecureKeyComponents,CHARINDEX('{"Create":',c1.SecureKeyComponents)) - (CHARINDEX('{"Create":',c1.SecureKeyComponents) +LEN('{"Create":') )) AS DECIMAL(20,5)) 
    		 END AS SecureKey_Create
    
    		 --INT Components
    		 --,c1.INTComponents
    		 ,CASE 
    			WHEN CHARINDEX('{"GenUCI":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"GenUCI":',c1.MCMComponents) + LEN('{"GenUCI":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"GenUCI":',c1.MCMComponents)) - (CHARINDEX('{"GenUCI":',c1.MCMComponents) +LEN('{"GenUCI":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS INT_GenUCI
    		,CASE 
    			WHEN CHARINDEX('{"SecureKeyDecryption":',c1.MCMComponents) > 0 THEN CAST(SUBSTRING(c1.MCMComponents,CHARINDEX('{"SecureKeyDecryption":',c1.MCMComponents) + LEN('{"SecureKeyDecryption":'),CHARINDEX('}',c1.MCMComponents,CHARINDEX('{"SecureKeyDecryption":',c1.MCMComponents)) - (CHARINDEX('{"SecureKeyDecryption":',c1.MCMComponents) +LEN('{"SecureKeyDecryption":') )) AS DECIMAL(20,5)) 
    			ELSE NULL
    		 END AS INT_SecureKeyDecryption
    
    		 --Host Timing
    		 --,c1.HostTimingComponents
    		,CASE 
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN SUBSTRING(c1.HostTimingComponents,CHARINDEX('(',c1.HostTimingComponents) + 1,CHARINDEX('):',c1.HostTimingComponents,CHARINDEX('(',c1.HostTimingComponents))  - CHARINDEX('(',c1.HostTimingComponents)-1)
    			ELSE NULL
    		 END AS ProcessorName
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE
    					WHEN CHARINDEX('{"Total":[{"TXN":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"Total":[{"TXN":',c1.HostTimingComponents) + LEN('{"Total":[{"TXN":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"Total":[{"TXN":',c1.HostTimingComponents)) - (CHARINDEX('{"Total":[{"TXN":',c1.HostTimingComponents) +LEN('{"Total":[{"TXN":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				END 
    			ELSE NULL
    		 END AS HostTiming_Total
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"GetStore":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"GetStore":',c1.HostTimingComponents) + LEN('{"GetStore":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"GetStore":',c1.HostTimingComponents)) - (CHARINDEX('{"GetStore":',c1.HostTimingComponents) +LEN('{"GetStore":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_DB_getStore
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"SelectRecentBatch":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"SelectRecentBatch":',c1.HostTimingComponents) + LEN('{"SelectRecentBatch":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"SelectRecentBatch":',c1.HostTimingComponents)) - (CHARINDEX('{"SelectRecentBatch":',c1.HostTimingComponents) +LEN('{"SelectRecentBatch":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_DB_SelectRecentBatch
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"SelectTargetBatch":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"SelectTargetBatch":',c1.HostTimingComponents) + LEN('{"SelectTargetBatch":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"SelectTargetBatch":',c1.HostTimingComponents)) - (CHARINDEX('{"SelectTargetBatch":',c1.HostTimingComponents) +LEN('{"SelectTargetBatch":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_DB_SelectTargetBatch
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"UpdateStoreOnBatchChange":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"UpdateStoreOnBatchChange":',c1.HostTimingComponents) + LEN('{"UpdateStoreOnBatchChange":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"UpdateStoreOnBatchChange":',c1.HostTimingComponents)) - (CHARINDEX('{"UpdateStoreOnBatchChange":',c1.HostTimingComponents) +LEN('{"UpdateStoreOnBatchChange":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_DB_UpdateStoreOnBatchChange
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"SendRecv":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"SendRecv":',c1.HostTimingComponents) + LEN('{"SendRecv":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"SendRecv":',c1.HostTimingComponents)) - (CHARINDEX('{"SendRecv":',c1.HostTimingComponents) +LEN('{"SendRecv":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_SendRecv
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"Connect":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"Connect":',c1.HostTimingComponents) + LEN('{"Connect":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"Connect":',c1.HostTimingComponents)) - (CHARINDEX('{"Connect":',c1.HostTimingComponents) +LEN('{"Connect":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_Connect
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"WantENQ":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"WantENQ":',c1.HostTimingComponents) + LEN('{"WantENQ":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"WantENQ":',c1.HostTimingComponents)) - (CHARINDEX('{"WantENQ":',c1.HostTimingComponents) +LEN('{"WantENQ":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_WantENQ
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"Transmit":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"Transmit":',c1.HostTimingComponents) + LEN('{"Transmit":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"Transmit":',c1.HostTimingComponents)) - (CHARINDEX('{"Transmit":',c1.HostTimingComponents) +LEN('{"Transmit":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_Transmit
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"Receive":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"Receive":',c1.HostTimingComponents) + LEN('{"Receive":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"Receive":',c1.HostTimingComponents)) - (CHARINDEX('{"Receive":',c1.HostTimingComponents) +LEN('{"Receive":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_Receive
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"SSLAuthenticate":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"SSLAuthenticate":',c1.HostTimingComponents) + LEN('{"SSLAuthenticate":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"SSLAuthenticate":',c1.HostTimingComponents)) - (CHARINDEX('{"SSLAuthenticate":',c1.HostTimingComponents) +LEN('{"SSLAuthenticate":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Host_SSLAuthenticate
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"LookupRule":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"LookupRule":',c1.HostTimingComponents) + LEN('{"LookupRule":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"LookupRule":',c1.HostTimingComponents)) - (CHARINDEX('{"LookupRule":',c1.HostTimingComponents) +LEN('{"LookupRule":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Processing_LookupRule
    		,CASE
    			WHEN LEN(c1.HostTimingComponents) > 0 THEN 
    				CASE 
    					WHEN CHARINDEX('{"Delay":',c1.HostTimingComponents) > 0 THEN CAST(SUBSTRING(c1.HostTimingComponents,CHARINDEX('{"Delay":',c1.HostTimingComponents) + LEN('{"Delay":'),CHARINDEX('}',c1.HostTimingComponents,CHARINDEX('{"Delay":',c1.HostTimingComponents)) - (CHARINDEX('{"Delay":',c1.HostTimingComponents) +LEN('{"Delay":') )) AS DECIMAL(20,5)) 
    					ELSE NULL
    				 END 
    			ELSE NULL	
    		 END AS HostTiming_Processing_Delay
    
    --		 --New or Old Path
    		,CASE CHARINDEX('"MCM":[]',c1.ComponentTotals) 
    			WHEN  0 THEN 'New Path' 
    			ELSE 'Old Path'
    		 END AS PathType
    
    		--Header info
    		--,c1.HeaderInfo
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX('[',c1.HeaderInfo) + LEN('['),4) AS ChainCode
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX('[',c1.HeaderInfo) + LEN('[') + 5,4) AS StoreCode
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX(']',c1.HeaderInfo) -36,36) AS ClientTransactionId
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX('amt=',c1.HeaderInfo) + LEN('amt=') ,CHARINDEX(',',c1.HeaderInfo,CHARINDEX('amt=',c1.HeaderInfo)) -CHARINDEX('amt=',c1.HeaderInfo) - 4) AS Amount
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX('rsp=',c1.HeaderInfo) +LEN('rsp="'), CHARINDEX(',',c1.HeaderInfo,CHARINDEX('rsp=',c1.HeaderInfo) +LEN('rsp="') ) - (CHARINDEX('rsp=',c1.HeaderInfo) +LEN('rsp="') ) -1) AS HostResponse
    		,SUBSTRING(c1.HeaderInfo,CHARINDEX('card=',c1.HeaderInfo) +LEN('card="'), CHARINDEX(']',c1.HeaderInfo,CHARINDEX('card=',c1.HeaderInfo) +LEN('card="') ) - (CHARINDEX('card=',c1.HeaderInfo) +LEN('card="') )-1 ) AS CardType
    		,CASE
    			WHEN CHARINDEX('ot=KT1',c1.HeaderInfo) > 0 THEN SUBSTRING(c1.HeaderInfo,CHARINDEX('ot=KT1',c1.HeaderInfo) + LEN('ot=KT1'),CHARINDEX(',',c1.HeaderInfo,CHARINDEX('ot=KT1',c1.HeaderInfo)) - (CHARINDEX('ot=KT1',c1.HeaderInfo) +LEN('ot=KT1') ))
    			ELSE NULL
    		 END AS TransType
    		,CASE 
    			WHEN CHARINDEX('tt=KT1',c1.HeaderInfo) > 0 THEN SUBSTRING(c1.HeaderInfo,CHARINDEX('tt=KT1',c1.HeaderInfo) + LEN('tt=KT1'),CHARINDEX(',',c1.HeaderInfo,CHARINDEX('tt=KT1',c1.HeaderInfo)) - (CHARINDEX('tt=KT1',c1.HeaderInfo) +LEN('ot=KT1') ))
    			ELSE NULL
    		 END AS tt
    --		,c1.[Message]
    	FROM ctePullme c1
    This is why I hate stuffing every thing into one big field
    Last edited by GaryMazzone; Yesterday at 07:02 AM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  13. #13
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,691

    Re: An interesting query based on a date buried in a big long string

    Quote Originally Posted by GaryMazzone View Post
    Dumping that all into a single blob string is difficult to work with (believe me I know) ...
    Impressive efforts on your end... (to bring that content "into shape").

    Though - judging from the structure of the Fields you were parsing out,
    the input-content seems to be in JSON-format.

    So I guess, when you'd use the JSON-support-functions of the given DBEngine,
    you could shorten this larger procedure to a third of its current volume or so...

    MS-SQLServer was a bit late to the game though, offering JSON-support only since Version 2012 IIRC...

    Olaf

  14. #14
    Lively Member
    Join Date
    Jun 2019
    Posts
    68

    Re: An interesting query based on a date buried in a big long string

    Quote Originally Posted by Schmidt View Post
    ....
    MS-SQLServer was a bit late to the game though, offering JSON-support only since Version 2012 IIRC...
    The sad part is that it is supported from 2016+ (Work with JSON Data in SQL Server) and still many companies use 2000, 2005, 2008...

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,113

    Re: An interesting query based on a date buried in a big long string

    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,391

    Re: An interesting query based on a date buried in a big long string

    Not avail on the version of SQL we are using. Also the field is 1024 but the input could be truncated during the insert so I don't always get properly formed JSON
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,691

    Re: An interesting query based on a date buried in a big long string

    Quote Originally Posted by GaryMazzone View Post
    Not avail on the version of SQL we are using.
    Also the field is 1024 but the input could be truncated during the insert so I don't always get properly formed JSON
    LOL, now - that's indeed "a real mess" you have to workaround there...

    @peterst
    Thanks for the clarification about SQLServer-2016 (apparently being the first version with JSON-support).

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width