# 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 (https://blog.sqlauthority.com)

## SQL SERVER – System Stored Procedure sys.sp_tables

• 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…

• Superb…..

• 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

• 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.

Sash