Tuesday, February 4, 2014

Get Numeric Value From Alpha Numeric String

http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/

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@intAlpha1'' )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