question

Well, the quick, dirty, unoptimized-for-readability way would be something like this: USE tempdb ; DECLARE @SampleData TABLE ( InputData VARCHAR(50) , ExpectedOutput BIGINT NULL ) ; INSERT INTO @SampleData VALUES ( '13558 - (vvgf) sdfs', 13558 ) , ( 'vfgt brand vfgt id 11251', 11251 ) , ( ' Gen+wert_Golf vgbh [BDEMM] [PsSCC]', NULL ) , ( ' GEE methods id 8767 ', 8767 ) , ( 'Competitors_HurytD 3 [BMM] [PC]', NULL ) , ( 'Gssss+werrr_Formula 1 frtgthy [BMM] [PC] ', NULL ) , ( 'online xasde exact t1 id 8759 ', 8759 ) , ( 'Csderfvggt_Position 3 [Effx] [PerDC]', NULL ) ; SELECT * , LEN(sd.InputData) AS LengthOfString , -- For diagnostics PATINDEX('%[0-9][0-9]%', InputData) AS StartOfNumericString , -- For diagnostics PATINDEX( '%[^0-9]%' , SUBSTRING( InputData , PATINDEX('%[0-9][0-9]%', InputData), LEN(sd.InputData) - ( 1 + PATINDEX('%[0-9][0-9]%', InputData)) ) ) AS EndOfNumericString , -- For diagnostics CASE WHEN PATINDEX('%[0-9][0-9]%', sd.InputData) > 0 THEN SUBSTRING( sd.InputData , PATINDEX('%[0-9][0-9]%', sd.InputData), CASE WHEN PATINDEX( '%[^0-9]%' , SUBSTRING( InputData , PATINDEX('%[0-9][0-9]%', InputData), LEN(sd.InputData) - ( PATINDEX('%[0-9][0-9]%', InputData)) ) ) = 0 THEN ( LEN(sd.InputData) - PATINDEX('%[0-9][0-9]%', sd.InputData)) + 1 ELSE PATINDEX( '%[^0-9]%' , SUBSTRING( InputData , PATINDEX('%[0-9][0-9]%', InputData), LEN(sd.InputData) - ( 1 + PATINDEX('%[0-9][0-9]%', InputData)) ) ) - PATINDEX('%[0-9][0-9]%', InputData) END ) ELSE NULL END FROM @SampleData AS sd ; This could (should) be refactored, though, as it's an ugly mess, to move the calculations into a single place. Here, I'm using a WITH construct to define an intermediate table that contains the calculations shown in the "for diagnostics" comments - this makes the main SELECT statement easier to read. I've missed out the bit that builds the sample data set, as it's exactly the same as above. WITH precalc AS ( SELECT InputData , ExpectedOutput , LEN(sd.InputData) AS LengthOfString , PATINDEX('%[0-9][0-9]%', InputData) AS StartOfNumericString , PATINDEX( '%[^0-9]%' , SUBSTRING( InputData , PATINDEX('%[0-9][0-9]%', InputData), LEN(sd.InputData) - ( 1 + PATINDEX('%[0-9][0-9]%', sd.InputData)) ) ) AS EndOfNumericString FROM @SampleData AS sd ) SELECT precalc.InputData, precalc.ExpectedOutput, CASE WHEN precalc.StartOfNumericString > 0 THEN SUBSTRING( precalc.InputData , precalc.StartOfNumericString, CASE WHEN precalc.EndOfNumericString = 0 THEN ( precalc.LengthOfString - precalc.StartOfNumericString) + 1 ELSE precalc.EndOfNumericString - precalc.StartOfNumericString END ) ELSE NULL END FROM precalc ;