Wednesday, March 5, 2014

T-SQL code to parse Numbers from a String


Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.

Let’s take example to understand the problem.

Input
Output
Hari12 Sharma34
1234
My 1st code is D$34A and 2nd code is E#078!!!
1342078
Hari# Sharma
 
$1#0?0 !?@1
1001

Here is the code to get the desired output:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
DECLARE @Position SMALLINT = 0
SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character
 
WHILE (@Position > 0)
BEGIN
    -- Replace alphabet with empty string.
    SET @Str = STUFF(@Str, @Position, 1, '')
   
    -- Find next alphabet
    SET @Position = PATINDEX('%[^0-9]%', @Str)
END
SELECT @Str Result
 

Result
-------------------------------------
1342078

 
We can also write function to encapsulate this code and use wherever required.


This above code can be optimized by using Tally table mentioned below:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'
 
DECLARE @Result VARCHAR(100)
SELECT  @Result = COALESCE(@Result, '') + Digit
FROM    (
        SELECT  SUBSTRING(@Str, t.ID, 1) AS Digit
        FROM    dbo.Tally t
        WHERE   t.ID <= LEN(@Str)
        AND     PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1
        ) stg
 
SELECT @Result Result




Result
-------------------------------------
1342078

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.