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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Get Numeric Value From Alpha Numeric String – Get Numbers Only

  1. i wish you would have posted this 2 weeks earlier! I ran into this problem just recently and i put together a slightly weight version and used a table input instead.

    alter procedure dbo.CleanDataFromAlpha
    (
    @i_alpha varchar(50),
    @o_decimal decimal(14, 5) out
    )
    as

    begin
    set nocount on
    declare @x table (i int identity(1,1), x varchar(50))

    insert @x(x) values(@i_alpha)

    while @@rowcount>0
    update
    @x
    set
    x=ltrim(rtrim(replace(x,substring(x,patindex(‘%[^0-9.]%’, x),1),”)))
    where
    patindex(‘%[^0-9.]%’,x) 0

    select @o_decimal=case when len(x)=0 then null else convert(decimal(14,5),x) end from @x where i=1
    end

    go

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

    You can adjust for the comma if necessary…

    Like

  2. Wouldn’t it be easier to implement a regex clr function and just use that in an in-line sql?

    Select cast (dbo.clrRegexExtract(‘abcd355,88efg’, ‘([0-9]*),([0-9]*)’,’\1.\2′) as float)

    The first parameter is the input string, the second parameter is the extraction regex, the third parameter builds the output string. I’m surprised we gave to build the regex function custom…

    Not only would you solve this problem but you’d make future problems easier.

    Patrick

    Like

  3. I have a table with 3 columns Name, Amount and Date/

    Records are like:-
    Name Amount Date
    ======================
    A1 100 1/10/2010
    B1 90 1/10/2010
    C1 78 1/10/2010
    A1 74 2/10/2010
    B1 100 2/10/2010
    C1 96 2/10/2010
    A1 67 3/10/2010
    B1 34 3/10/2010
    C1 54 3/10/2010

    I need the ouput like this

    Name Amount Date Name Amount Date Name Amount Date
    1/10/2010 A1 100 2/10/2010 A1 74 3/10/2010 A1 67
    1/10/2010 B1 90 2/10/2010 B1 100 3/10/2010 B1 34
    1/10/2010 C1 78 2/10/2010 C1 96 3/10/2010 C1 54

    Is is possible to write sql query to get this output.

    Please help me If any one knows this concept.

    Thanks in advance
    Sash

    Like

  4. Hi,

    Can anyone help regarding wriing a store procedure for the following.

    1.I have string it has data of 12,15,16,17 or 12,17&21 etc
    i need to check the string consists of comma seperated if then i need to insert it into one table
    else if it has different seperation i need to log it has an error.
    can you kindly help me.

    Regards,
    Gopi

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s