Hi Tech !
I have written this awhile ago. Hopefully this will help you;
http://gallery.technet.microsoft.com/DataCleanUp-Function-5f2d1c30
USE [AdventureWorks]
GO
IF OBJECT_ID (N'dbo.DataCleanUp', N'FN') IS NOT NULL
DROP FUNCTION dbo.DataCleanUp;
GO
CREATE FUNCTION [dbo].[DataCleanUp](@Data VARCHAR(MAX),@InValidData VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @CleanUpData VARCHAR(MAX)
SET @CleanUpData = ' ' + @Data + ' '
WHILE PATINDEX(@InValidData,@CleanUpData)>0
BEGIN
SET @CleanUpData = STUFF(@CleanUpData,PATINDEX(@InValidData,@CleanUpData),1,'')
END
RETURN LTRIM(RTRIM(@CleanUpData))
END
Please let me know if this doesn’t work for you. Hope I have answered you correctly.
Thanks,
Hasham