Published on

September 5, 2010

Understanding SQL Server: Cleaning Alpha Numeric Data

Have you ever encountered a situation where you needed to extract numeric values from an alpha numeric string in SQL Server? If so, you’re not alone. Many developers face this challenge when working with data that contains a mix of letters and numbers. In a previous article, I shared a user-defined function (UDF) that can help you achieve this task. However, today I want to introduce you to an improved solution contributed by SQL expert Christofer.

Christofer has taken the original logic and enhanced it by providing a stored procedure that can easily be converted into a function. Let’s take a closer look at his contribution:

CREATE PROCEDURE [dbo].[CleanDataFromAlpha] 
    @alpha VARCHAR(50), 
    @decimal DECIMAL(14, 5) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ErrorMsg VARCHAR(50)
    DECLARE @Pos INT
    DECLARE @CommaPos INT
    DECLARE @ZeroExists INT
    DECLARE @alphaReverse VARCHAR(50)
    DECLARE @NumPos INT
    DECLARE @Len INT

    -- 1 Reverse the alpha in order to get the last position of a numeric value
    SET @alphaReverse = REVERSE(@alpha)

    -- 2 Get the last position of a numeric figure
    SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)

    -- 3 Get the length of the string
    SET @Len = LEN(@alpha)

    -- 4 Add a comma after the numeric data in case it's not a decimal number
    SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1)) + ',' + SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)

    -- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
    -- if it's 0 after the handling, else we set @decimal to NULL
    -- If 0 no match, else there is a match
    SET @ZeroExists = CHARINDEX('0', @alpha, 1)

    -- Find position of , (comma)
    SET @CommaPos = 1
    SET @CommaPos = PATINDEX('%,%', @alpha)

    IF (@CommaPos = '')
    BEGIN
        SET @CommaPos = 20
    END

    SET @Pos = PATINDEX('%[^0-9]%', @alpha)

    -- Replaces any alpha with '0' since we otherwise can't keep track of where the decimal
    -- should be put in. We assume the numeric number has no alpha inside. The regular way
    -- to solve this is to replace with ”, but then we miss the way to find the place to
    -- put in the decimal.
    WHILE (@Pos > 0)
    BEGIN
        SET @alpha = STUFF(@alpha, @pos, 1, '0')
        SET @Pos = PATINDEX('%[^0-9]%', @alpha)
    END

    IF (@alpha IS NOT NULL AND @alpha != '')
    BEGIN
        SET @decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1)) + '.' + SUBSTRING(@alpha, (@CommaPos + 1), 20))
    END

    -- Since we don't want to set 0 if there is no numeric value, we set NULL to be safe
    IF (@decimal = 0 AND @ZeroExists = 0)
    BEGIN
        SET @decimal = NULL
    END
END
GO

If you run the stored procedure as shown below, it will work:

DECLARE @myRetVal DECIMAL(14, 5)
EXEC [CleanDataFromAlpha] 'ABC355,88ghf', @myRetVal OUTPUT
SELECT @myRetVal ReturnValue

The output of the above code will be:

---------------------------------------
355.88000
(1 row(s) affected)

I would like to extend my gratitude to Christofer for his excellent contribution. His stored procedure provides an improved solution for extracting numeric values from alpha numeric strings in SQL Server.

Next time you encounter a similar challenge, consider using this enhanced logic to simplify your code and achieve the desired results.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.