Thursday, May 28, 2009

SQL Server function to strip non numeric characters

This is useful if you have a phone/fax number field and want to remove any non numerical characters. for eg: (301) 123-4567 to 3011234567


calling this function like: select [dbo].[FilterNonDigit]('123-456-7890') will return 1234567890

CREATE FUNCTION [dbo].[FilterNonDigit]( @Input varchar(256))
RETURNS varchar(256)
AS
BEGIN
If PATINDEX('%[^0-9]%', @Input) > 0
WHILE PATINDEX('%[^0-9]%', @Input) > 0
SET @Input = Stuff(@Input, PATINDEX('%[^0-9]%', @Input), 1, '')
RETURN @Input
END


http://www.DaksaTech.com
Web technology solutions for small and medium enterprises.

No comments: