CODEWITHSUNDEEP
sqlsql-servert-sql
Darius
Darius

Reputation: 5

T-SQL - Remove everything before and after in a string

Please assist me with the SQL query shown below.

This is the error I get:

System.Exception: TransformerException ---> Library.Utilities.TransformerException: Transformer transforming [0000000000085153227:documentId] using [4:transformId] with time remaining [00:00:54.4517645:TimeSpan] output document is [0000000000085153231:documentId] ---> and so on...

I'm training to retrieve this value 85153227 only.

Please assist

SELECT  
    SUBSTRING(message, 0, CHARINDEX('System.Exception', message)) + ' ' +  
    CASE          
       WHEN CHARINDEX('Transformer transforming [', message) > 0
          THEN SUBSTRING(message, CHARINDEX('[',message), LEN(message)-CHARINDEX('[', message)) 
          ELSE '' 
    END
FROM
    dbo.Documents

Thank you

Upvotes: 0

Views: 391

Answers (5)

Alan Burstein
Alan Burstein

Reputation: 7918

I came up with:

DECLARE @string varchar(8000) = 
'System.Exception: TransformerException ---> Library.Utilities.TransformerException: Transformer transforming [0000000000085153227:documentId] using [4:transformId] with time remaining [00:00:54.4517645:TimeSpan] output document is [0000000000085153231:documentId] --->'

SELECT cast(substring(@string, p1.p, p2.p-p1.p) as bigint)
FROM (VALUES (patindex('%[0-9]%', @string))) p1(p)
CROSS APPLY (VALUES (charindex(':', @string, p1.p+1))) p2(p);

Output:

85153227

Upvotes: 0

GeorgiG
GeorgiG

Reputation: 1103

You can try this:

with MsgWithZeroesTbl as 
(SELECT  'System.Exception ' + 
        CASE      
            WHEN charindex('Transformer transforming [',message) >= 0
            THEN substring(message,charindex('[',message) + 1, charindex(':',message, charindex('[',message))- (charindex('[',message) + 1)) ELSE '' 
        END as MsgWithZeros
        FROM dbo.Documents )
Select substring(MsgWithZeros, patindex('%[^0]%',MsgWithZeros), 10) from MsgWithZeroesTbl

Upvotes: 0

Joe C
Joe C

Reputation: 3993

Based on what you provided I modified your query a bit to get the output you want. You may need to adjust depending on your data.

SELECT  substring(message,0,charindex('System.Exception',message)) + ' ' + 
CASE          WHEN charindex('Transformer transforming [',message) > 0
          THEN Convert(Varchar(8000), Convert(BigInt, Substring(message,charindex('[',message) + 1, charindex(':documentid]',message)-(CharIndex('[',message)+1)))) ELSE '' END
FROM dbo.Documents

Upvotes: 0

JoeFletch
JoeFletch

Reputation: 3960

It's hard to determine an exact response to this because you did not identify if the exception is always structured the same. With what is provided, this is what I would do.

  1. Removing the leading string of characters with STUFF; STUFF([Message], 1, CHARINDEX(N'Transformer transforming [', [Message]) + 25, N'')
  2. Then assuming that the string of characters with the number is 19 characters long, I then do a SUBSTRING to find the first non-zero character. SUBSTRING({field}, PATINDEX('%[^0]%', {field} + '.'), LEN({field})) substituting step 1 into this string for {field} (I tried to simplify the code here).

Here is the SQL statement showing this.

IF OBJECT_ID(N'tempdb..#t') IS NOT NULL DROP TABLE #t
SELECT CAST('System.Exception: TransformerException ---> Library.Utilities.TransformerException: Transformer transforming [0000000000085153227:documentId] using [4:transformId] with time remaining [00:00:54.4517645:TimeSpan] output document is [0000000000085153231:documentId] ' AS NVARCHAR(MAX))
    AS [Message]
    INTO #t

SELECT *,
    STUFF([Message], 1, CHARINDEX(N'Transformer transforming [', [Message]) + 25, N'') AS BeginningCharactersStripped,
    SUBSTRING(LEFT(STUFF([Message], 1, CHARINDEX(N'Transformer transforming [', [Message]) + 25, N''), 19), PATINDEX('%[^0]%', LEFT(STUFF([Message], 1, CHARINDEX(N'Transformer transforming [', [Message]) + 25, N''), 19) + '.'), LEN(LEFT(STUFF([Message], 1, CHARINDEX(N'Transformer transforming [', [Message]) + 25, N''), 19)))
        AS Number
FROM #t
WHERE [Message] like N'%Transformer transforming [[]%'

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2011

DECLARE @ AS VARCHAR(MAX)= 
'System.Exception: TransformerException ---> Library.Utilities.TransformerException: Transformer transforming [0000000000085153227:documentId] using [4:transformId] with time remaining [00:00:54.4517645:TimeSpan] output document is [0000000000085153231:documentId]'

SELECT 
CAST(
SUBSTRING(@,
    CHARINDEX('Transformer transforming [',@,0) + LEN('Transformer transforming [')
,   CHARINDEX(':documentId]',@,0) - ( CHARINDEX('Transformer transforming [',@,0) + LEN('Transformer transforming [') ))
AS BIGINT)

--OUTPUT

--------------------
85153227

(1 row affected)

Upvotes: 1

Related Questions