SQL : Return first integer after a delimiter

Question

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

Is there always a space or something that terminates the integer? — SQLChao, Dec 10 '15 at 20:51
See http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/ — PM 77-1, Dec 10 '15 at 20:55
@JCHao Unfortunately no. If the number is the last string in the message, it simply reaches the end of the record. — osboy1, Dec 10 '15 at 21:50

score 0 · Answer 1 · answered Dec 10 '15 at 21:15

Use CHARINDEX with a SUBSTRING function to evaluate the first two characters and provide the desired output.

--RECREATE THE DATA
if object_id('tempdb..#input') is not null
    drop table #input

create table #input (inRec varchar(max))

insert #input
values
('Job succeeded- exit code: 12345'),
('Job succeeded- exit code: 678 - some message.'),
('Job succeeded- exit code: -98765431 - a different message')

--EVALUATE THE MESSAGE BASED ON KNOWN PATTERNS
select case 
    when left(tempRec,1) like '[%0123456789%]' then left(tempRec,1) 
    else right(tempRec,1)
    end as FirstNum
from 
(
    SELECT substring(inRec,(charindex(':',inRec)+2),2) tempRec
    FROM #input
)x

score 0 · Accepted Answer · answered Dec 10 '15 at 21:37

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.

score 0 · Answer 3 · answered Dec 10 '15 at 22:34

0

If you can rely on that constant prefix and you just want a not-very-robust hack...

substring(s, 26, charindex(' ', replace(s + ' ', 'Job succeeded- exit code: ', '')))

s is the column name.

answered Dec 10 '15 at 22:34

shawnt00

16,443
3
17
22

Unfortunately I can not count on the constant prefix, although this snippet proved useful on a related query in another table. – osboy1 Dec 11 '15 at 05:37

SQL : Return first integer after a delimiter

3 Answers3