Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Tagged as

Finding numbers from a text in SQL Server.

, 10 Mar 2012
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "Finding numbers from a text in SQL Server."

Introduction 

The goal of the original tip was to get the numbers from a text. The numbers could be separated by any non-digit character.

When I first looked at the original tip a few things caught my eye:
    1. The many times the SUBSTRING function was called.
    2. The need to look ahead in the string to determine if the number was finished.
    3. The use of the Split function to get the result in the return table.

This is my attempt at a more efficient way of retrieving the numbers from the text.

The function 

CREATE FUNCTION dbo.GetNumbersFromText( @String VARCHAR(2000) )
RETURNS @Number TABLE( Number INT )

BEGIN
    DECLARE @Count INT = 0;
    DECLARE @Character VARCHAR(1) = ''
    DECLARE @NumberText VARCHAR(2000) = ''
    
    WHILE @Count <= LEN( @String )
    BEGIN
        -- Get current character from the string.
        SET @Character = SUBSTRING( @String, @Count, 1 )

        -- Character is a digit, add it to end of the number already found.
        IF( @Character >= '0' AND @Character <= '9' )
        BEGIN
            SET @NumberText = @NumberText + @Character
        END
        -- Character is not digit, skip it. If we already found a number store it in the result table.
        ELSE
        BEGIN
            IF( LEN( @NumberText ) > 0 )
            BEGIN
                INSERT INTO @Number SELECT @NumberText
                SET @NumberText = ''
            END
        END
        
        -- Move onto the next character
        SET @Count = @Count + 1
    END
    
    -- If the last number was at the end of the string add it to the result table.
    IF( LEN( @NumberText ) > 0 )
    BEGIN
        INSERT INTO @Number SELECT @NumberText
        SET @NumberText = ''
    END
    
    RETURN
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

André Kraak
Software Developer
Netherlands Netherlands
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 10 Mar 2012
Article Copyright 2012 by André Kraak
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid