question

hsharathchandra avatar image
hsharathchandra asked

How to extract number which is of minimum length 2 from a string in SQL SERVER. Please see sample data

SAMPLE DATA INPUT AND OUTPUT (i) .13558 - (vvgf) sdfs -> 13558 (ii). vfgt brand vfgt id 11251 -> 11251 (iii). Gen+wert_Golf vgbh [BDEMM] [PsSCC] -> (null) (iv). GEE methods id 8767 -> 8767 (v). Competitors_HurytD 3 [BMM] [PC] -> (null) (vi). Gssss+werrr_Formula 1 frtgthy [BMM] [PC] -> (null) (vii). online xasde exact t1 id 8759 -> 8759 (viii). Csderfvggt_Position 3 [Effx] [PerDC] -> (null)
sql serversubstringregex
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
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 ;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.