Need to Extract Integers from Sting

Question

I have a Column which a mixture of String and Integer

E.g  DQ1-12987, DQQ-12367, DS18768, AS-10383B

I need to extract only Integers from the Column but if the Data has Integer Before '-' I don't want to consider that Integer.

e.g DQ1-12987 should be 12987 or DQQ-12387 should be as 12387

I am using the below code but this is not giving me correct result.

Create Function dbo.GetNumbers(@Data VarChar(8000))
Returns VarChar(8000)
AS
Begin 
    Return Left(
             SubString(@Data, PatIndex('%[0-9]%', @Data), 8000), 
             PatIndex('%[^0-9]%', SubString(@Data, PatIndex('%[0-9]%', @Data), 8000) + 'X')-1)
End

How can I achieve this

What about the other examples? Should `AS-10383B` therefore return nothing? — Thom A, May 26 '20 at 15:23
What does this mean? `if the Data has Integer after '-' I don't want to consider that Integer.` All of your samples violate that requirement. — Eric Brandt, May 26 '20 at 15:49

score 0 · Answer 1 · answered May 26 '20 at 15:19

0

Is this what you want?

select (case when v.data not like '%[0-9]%-%' and
                  v.data like '%[0-9]%'
             then left(v2.data, patindex('%[^0-9]%', v2.data + 'x') - 1)
        end)
from (values (@data)) v(data) cross apply
     (values (stuff(v.data, 1, patindex('%[0-9]%', v.data)) v2(data)

answered May 26 '20 at 15:19

Gordon Linoff

1,242,037
58
646
786

It giving me null for values like DQ1-12987 – Sarita May 26 '20 at 16:10
@Sarita . . . That is what you are asking for: "I need to extract only Integers from the Column but if the Data has Integer Before '-' I don't want to consider that Integer." At least, that is how I interpret your question. – Gordon Linoff May 26 '20 at 18:56

score 0 · Answer 2 · answered May 26 '20 at 16:21

For this grab a copy of patExtract8K. Then it's easy.

Against a string it looks like this:

DECLARE @string VARCHAR(8000) = 'E.g  DQ1-12987, DQQ-12367, DS18768, AS-10383B';

SELECT
  ItemNumber = ROW_NUMBER() OVER (ORDER BY f.ItemNumber),
  ItemIndex  = f.ItemIndex,
  ItemLength = f.ItemLength, 
  Item       = f.Item
FROM   samd.patExtract8k(@string,'[^0-9]') AS f
WHERE  SUBSTRING(@string,f.itemIndex+1,1) <> '-';

Returns:

ItemNumber  ItemIndex   ItemLength  Item
----------- ----------- ----------- --------
1           10          5           12987
2           21          5           12367
3           30          5           18768
4           40          5           10383

Against a table:

-- Sample Data
CREATE TABLE #strings (stringId INT IDENTITY, string VARCHAR(1000));
INSERT #strings(string)
SELECT TOP(10) NEWID() FROM sys.all_columns;

--Solution:
SELECT * 
FROM        #strings                             AS s
CROSS APPLY samd.patExtract8k(s.string,'[^0-9]') AS f
WHERE       SUBSTRING(s.string,f.itemIndex+1,1) <> '-';

Returns (truncated for brevity):

stringId    string                                  itemNumber  itemIndex  itemLength  item
----------- --------------------------------------- ----------- ---------- ----------- ----------
1           8703661F-84D9-4AFC-9E8F-918926121A49    1           1          7           8703661
1           8703661F-84D9-4AFC-9E8F-918926121A49    2           10         2           84
1           8703661F-84D9-4AFC-9E8F-918926121A49    4           15         1           4
1           8703661F-84D9-4AFC-9E8F-918926121A49    5           20         1           9
1           8703661F-84D9-4AFC-9E8F-918926121A49    6           22         1           8
1           8703661F-84D9-4AFC-9E8F-918926121A49    7           25         9           918926121
1           8703661F-84D9-4AFC-9E8F-918926121A49    8           35         2           49
2           C93D48BD-9485-4E8E-9295-2271726F08C6    1           2          2           93
2           C93D48BD-9485-4E8E-9295-2271726F08C6    2           5          2           48
2           C93D48BD-9485-4E8E-9295-2271726F08C6    3           10         4           9485
2           C93D48BD-9485-4E8E-9295-2271726F08C6    4           15         1           4
2           C93D48BD-9485-4E8E-9295-2271726F08C6    5           17         1           8
2           C93D48BD-9485-4E8E-9295-2271726F08C6    6           20         4           9295
2           C93D48BD-9485-4E8E-9295-2271726F08C6    7           25         7           2271726
2           C93D48BD-9485-4E8E-9295-2271726F08C6    8           33         2           08
2           C93D48BD-9485-4E8E-9295-2271726F08C6    9           36         1           6

The function is blazing fast and will be any scalar UDF on performance.

Need to Extract Integers from Sting

2 Answers2