dbo.fn_StripPunctuation

IF EXISTS ( SELECT *
            FROM   dbo.sysobjects
            WHERE  id = object_id (N'dbo.fn_StripPunctuation')
                   AND type IN ( N'FN', N'IF', N'TF') )
BEGIN
    DROP FUNCTION dbo.fn_StripPunctuation;
END
GO
CREATE FUNCTION dbo.fn_StripPunctuation
( @Text VARCHAR(8000) )
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Result VARCHAR(100) = '';

IF @Text LIKE '%[^a-z^0-9]%'
    BEGIN
        SELECT @Result = @Result + SUBSTRING(@Text, N.n, 1)
        FROM   dbo.tvf_TallyTable(1000) as N
        WHERE  N.n <= LEN(@Text)
               AND SUBSTRING(@Text, N.n, 1) LIKE '[0-9a-z]';
    END
ELSE
    BEGIN
        SET @Result = @Text
    END
RETURN @Result;
END
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s