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 comments. Leave new

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

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

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

Superb…..

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…