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.

SQL Server Function to get date without time

CREATE FUNCTION [dbo].[getDateWithoutTime](
@date datetime
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @returnDate smalldatetime
SET @returnDate = cast(cast(month(@date) as varchar(2)) + '-' + cast(day(@date) as varchar(2)) + '-' + cast(year(@date) as varchar(4)) as smalldatetime)
RETURN @returnDate
END


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