substring with changing length

Question

Post reply

substring with changing length

PHXHoward

SSCrazy Eights

Points: 8389
More actions
September 19, 2011 at 3:00 pm

#244021

Hi, I'm trying to select the number after "job processed" from [column 5]. Sometimes the number is one digit, sometimes two, sometimes it could be more. What can I specify for the substring length to catch the entire number?
Example [column 5] rows:
[jobname] [RPP] Info: [RPP] Job processed 0, Success: 0, Error: 0.
[jobname] [RPP] Info: [RPP] Job processed 43, Success: 43, Error: 0.
[jobname] [RPP] Info: [RPP] Job processed 120, Success: 120, Error: 0.
This query works if the number is 2 digits.
select (substring([column 5],patindex('%Job processed %',[column 5]) + 14,2))
from stg_reporting
Thanks for reading. Hope to see some of you at SQL PASS.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

This:

DECLARE @tab TABLE

( [Column 5] VARCHAR(200) )

INSERT INTO @tab

SELECT '[jobname] [RPP] Info: [RPP] Job processed 0, Success: 0, Error: 0.'

UNION ALL SELECT '[jobname] [RPP] Info: [RPP] Job processed 43, Success: 43, Error: 0.'

UNION ALL SELECT '[jobname] [RPP] Info: [RPP] Job processed 120, Success: 120, Error: 0'

DECLARE @Lgt INT = LEN('Job processed')

; WITH cte AS

(

SELECT LTRIM( STUFF([Column 5] , 1, CHARINDEX('Job processed',[Column 5] ) + @Lgt , '') ) AS [Column 5]

FROM @tab

)

SELECT LEFT ( [Column 5], CHARINDEX(',',[Column 5])-1) AS Numb

FROM cte

Lowell SSC Guru Points: 323518 More actions · Answer 2

i'm using the patindex a second time to find the comma, and then pulling the length of whatever is between the two.

with stg_reporting([column 5])

AS (

SELECT '[jobname] [RPP] Info: [RPP] Job processed 0, Success: 0, Error: 0.' UNION ALL

SELECT '[jobname] [RPP] Info: [RPP] Job processed 43, Success: 43, Error: 0.' UNION ALL

SELECT '[jobname] [RPP] Info: [RPP] Job processed 120, Success: 120, Error: 0.' UNION ALL

SELECT '[jobname] [RPP] Info: [RPP] Job processed 15,897.11, Success: 120, Error: 0.'

)

select (substring([column 5],patindex('%Job processed %',[column 5]),patindex('%,%',[column 5]) - patindex('%Job processed %',[column 5])))

from stg_reporting

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Lowell (9/19/2011)
i'm using the patindex a second time to find the comma, and then pulling the length of whatever is between the two.
with stg_reporting([column 5])
AS (
SELECT '[jobname] [RPP] Info: [RPP] Job processed 0, Success: 0, Error: 0.' UNION ALL
SELECT '[jobname] [RPP] Info: [RPP] Job processed 43, Success: 43, Error: 0.' UNION ALL
SELECT '[jobname] [RPP] Info: [RPP] Job processed 120, Success: 120, Error: 0.' UNION ALL
SELECT '[jobname] [RPP] Info: [RPP] Job processed 15,897.11, Success: 120, Error: 0.'
)
select (substring([column 5],patindex('%Job processed %',[column 5]),patindex('%,%',[column 5]) - patindex('%Job processed %',[column 5])))
from stg_reporting

one small correction, if there are any commas before "job processed", then the code will fail

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

Thanks for both of the examples. Both helped me get the query to work.

Howard