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

It has been one of my projects that helped me with this logic, which is getting Numeric Value From Alpha Numeric String – UDF. Below is the Stored Procedure that 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)

Thanks again to Christofer for his excellent contribution.

DELETE Top TSQL Statement.

A new addition to the DELETE command in SQL Server 2005 is the TOP statement. The DELETE TOP does the same thing as a SELECT TOP WHERE only the TOP number of rows are deleted.

This can be very helpful when there are duplicate rows of data present.

DELETE TOP (1)
FROM Sales.Customer
WHERE CustomerID = 1

This would delete one of the duplicate rows for Customer number 1

Suppose somehow the whole customer table got duplicated. I duplicated the Sales.Customer table into a tmpCustomer table.

SELECT Top 1 CustomerID, COUNT(CustomerID) AS Cnt
FROM tmpCustomer
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1

WHILE @@RowCount > 0
BEGIN
DELETE Top (1)
FROM tmpCustomer
WHERE CustomerID = (SELECT Top (1) CustomerID
FROM tmpCustomer
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1)

END

While this worked just fine, it ran about 4 minutes for 38K rows. Let's try the dreaded CURSOR. Notice I can stick a variable in where the TOP () statement is. I subtracted -1 because we don't want to delete every row.

DECLARE @cnt int, @custID as int

DECLARE dupCursor CURSOR FAST_FORWARD
FOR SELECT CustomerID, COUNT(CustomerID) AS Cnt
FROM tmpCustomer
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1

OPEN dupCursor

FETCH NEXT FROM dupCursor
INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE Top (@cnt-1)
FROM tmpCustomer
WHERE CustomerID = @custID

FETCH NEXT FROM dupCursor
INTO @custID, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor