SQL SERVER – Get Numeric Value From Alpha Numeric String – Get Numbers Only

I have earlier wrote article about SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only and it was very handy tool for me. Recently blog reader and SQL Expert Christofer has left excellent improvement to this logic. Here is his contribution. He has provided Stored Procedure and the same can be easily converted to Function.

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 lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no 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 aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe 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 in this case don't want to set 0 if where 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 above SP as shown below it will work
DECLARE @myRetVal DECIMAL(14,5)
EXEC [CleanDataFromAlpha] 'ABC355,88ghf', @myRetVal OUTPUT
SELECT @myRetVal ReturnValue

ReturnValue
---------------------------------------
355.88000

(1 row(s) affected)

I once again want to thanks Christofer for his excellent contribution.

Test Before You Trust Any Parser

This procedure treats the comma as the decimal separator, so ABC355,88ghf becomes 355.88. If your data uses a dot for decimals, test it carefully before you use it. I also suggest trying a few tricky inputs, such as an empty string, a NULL value, a string with two separate numbers and a string longer than 50 characters, because the parameter is VARCHAR(50) and anything longer gets cut off.

When the value is already clean and you only need to check whether it converts, SQL Server 2012 and later offer TRY_CONVERT. It returns NULL instead of an error when the conversion fails.

SELECT TRY_CONVERT(DECIMAL(14,5), '355.88') AS GoodValue,
    TRY_CONVERT(DECIMAL(14,5), 'ABC355') AS BadValue;

The first column returns 355.88000 and the second returns NULL. Use TRY_CONVERT to validate clean data, and a routine like the one above when you need to pull a Numeric Value From Alpha Numeric String full of letters. Picking the right tool for the data saves a lot of debugging later. Before any of this reaches production, run it against a sample of your real data and compare the output with what a person would read. Every row that looks wrong tells you which rule to fix, and it is much cheaper to find that out on a test server.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.