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

SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote few days ago, which will return only Numeric values from AlphaNumeric values.

CREATE FUNCTION dbo.udf_GetNumeric(@strAlphaNumeric VARCHAR(256))RETURNS VARCHAR(256)AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)BEGIN
WHILE @intAlpha > 0BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )END
END
RETURN ISNULL(@strAlphaNumeric,0)ENDGO

/* Run the UDF with different test values */SELECT dbo.udf_GetNumeric('') AS 'EmptyString';SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';SELECT dbo.udf_GetNumeric('123456') AS '123456';SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';GO

As result of above script self explainary, I will be not describing it in detail. If you use any alternative method to do the same task, please let me know.