Monday, July 29, 2013

How to fetch all numeric values from the string?

In this article I will show how to fetch all numeric values from a string. 


I am creating a User defined function which will return all numeric values from the supplied string.
Function Code:
CREATE FUNCTION dbo.FetchNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
Example to Fetching Numeric Values from String:
SELECT dbo.FetchNumeric('jvhew832gj3hgj45')