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.