6

SQL: Return the first integer after a delimiter

 2 years ago
source link: https://www.codesd.com/item/sql-return-the-first-integer-after-a-delimiter.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL: Return the first integer after a delimiter

advertisements

My SQL database manages the metadata for a front end application which uses several executables. One of the columns in the job scheduler table is a list of acceptable return codes for each executable.

A different table receives the output of the executables. Some of the executables behave nicely and output a single integer value which is then easy to compare with the acceptable values mentioned above, using an IN clause.

The problem comes with several legacy executables which output verbose messages. For example one might output a string such as "Job succeeded- exit code: 12345" and one might output "Job succeeded- exit code: 678 - maximum run time exceeded". The text before and after the ":" will vary, but the next characters (apart from spaces) after the ":" will always be an integer.

I've found ways to keep everything after the delimiter, or a certain number of characters after the delimiter.

How do I extract the first INTEGER after the ":" delimiter, if I won't know the length of the integer ahead of time? Also some of the return codes include a leading "-" for a negative value.

input:

1   'Job succeeded- exit code: 12345'
2   'Job succeeded- exit code: 678 - some message.'
3   'Job succeeded- exit code: -98765431 - a different message'

Desired output:

1   12345
2   678
3   -98765431

This would allow me to compare the exit codes from these legacy executables against the list of acceptable codes defined in the jobs table.

UPDATE:

Both @Steve Mangiameli and @Herman 's answers were helpful. My challenges were that the text before the delimiter were not consistent nor of fixed length, and that the delimiter character ':' I wanted to use appears in some of the prefix text (for example, in a string pointing to a file path D:\somefile.csv).

I was lucky in that the log writer does some formatting so I was able to use the string '): ' in a PATINDEX statement. I came up with the expression below, combining the PATINDEX suggestions with a LEN function, with a REPLACE to get rid of the last ':'.

REPLACE(((RIGHT([LogEntry], LEN([LogEntry]) - PATINDEX('%):%', [LogEntry])))), ': ', '') as ReportedExitCode

I combined the above with the delimiter-splitting function described in this post to get my comma-delimited exit code list into an IN clause, and now the whole is working just as I had hoped.

SELECT a.[JOBITEM]
      ,a.[SUCCESSEXITCODES]
      ,REPLACE(((RIGHT([LogEntry], LEN([LogEntry]) - PATINDEX('%):%', [LogEntry])))), ': ', '') as ReportedExitCode
      ,b.[LogEntry]
FROM [JOBTABLE].[dbo].[LEGACY_JOBS] AS a
JOIN [JOBTABLE].[dbo].[PROCESS_LOGS] AS b ON b.jobitem = a.jobitem
WHERE b.[LogEntry] LIKE '%(success)%'
AND REPLACE(((RIGHT([LogEntry], LEN([LogEntry]) - PATINDEX('%):%', [LogEntry])))), ': ', '') NOT IN (
    SELECT value
    FROM dbo.splitDelimitedValues(a.[SuccessExitCodes], ',')
    )

OUTPUT:

JOBITEM SUCCESSEXITCODES    ReportedExitCode    LogEntry
Job1    0,1                 6                   ABCExec.exe Return Code (Success): 6
Job2    0                   1                   foo.bat Return Code (Success): 1
Job3    0,1                 1                   oldjob.bat Return Code (Success): -1
Job4    0,1                 -52789451           legacgyjob.exe Return Code (Success): -52789451


use tempdb
create table #test1 (ID int, Code varchar(max))

insert into #test1
values (1,'Job succeeded- exit code: 12345'),
(2,'Job succeeded- exit code: 678 - some message.'),
(3,'Job succeeded- exit code: -98765431 - a different message')        

        SELECT LEFT(SUBSTRING(Code, PATINDEX('%[0-9]%', Code), 8000),
        PATINDEX('%[^0-9]%', SUBSTRING(Code, PATINDEX('%[0-9]%', Code), 8000) + 'X') - 1) AS
        OUTPUT
        FROM #test1

This will extract the numbers from the text.. I'm separating this out so you can see what's going on and perhaps use this in the future.

The next step is to compare this against the previous text to see if any of the numbers are negative (this following code is the only code required to get your results)

select a.ID, case when left(ltrim(rtrim(replace(substring(b.Code,charindex(':',b.Code),charindex('-',b.Code)-1), ':',''))),1) = '-' then '-' + LEFT(SUBSTRING(a.Code, PATINDEX('%[0-9]%',a.Code), 8000),
           PATINDEX('%[^0-9]%', SUBSTRING(a.Code, PATINDEX('%[0-9]%', a.Code), 8000) + 'X') -1) else LEFT(SUBSTRING(a.Code, PATINDEX('%[0-9]%',a.Code), 8000),
           PATINDEX('%[^0-9]%', SUBSTRING(a.Code, PATINDEX('%[0-9]%', a.Code), 8000) + 'X') -1) end as output from #test1 a join #test1 b on a.id = b.id

Based on the comparison we get output

ID  output
==========
1   12345
2   678
3   -98765431

Hope this helps you out.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK