Monday, November 4, 2013

SQL - Remove Special Characters

Removes all special characters from an input string.

USE MIGRATION_DB
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID (N'dbo.RemoveSpecialChars', N'FN') IS NOT NULL
DROP FUNCTION dbo.RemoveSpecialChars;
GO
CREATE FUNCTION dbo.RemoveSpecialChars
( @InputString VARCHAR(max)
)
RETURNS VARCHAR(8000) BEGIN
IF @InputString IS NULL RETURN NULL

DECLARE @OutputString VARCHAR(max)
SET @OutputString = ''
DECLARE @l INT
SET @l = LEN(@InputString)
DECLARE @p INT
SET @p = 1
WHILE @p <= @l
BEGIN
DECLARE @c INT
SET @c = ASCII(SUBSTRING(@InputString, @p, 1))
IF @c BETWEEN 48 AND 57
OR @c BETWEEN 65 AND 90
OR @c BETWEEN 97 AND 122
--OR @c = 32
SET @OutputString = @OutputString + CHAR(@c)
SET @p = @p + 1
END IF LEN(@OutputString) = 0 RETURN NULL
RETURN @OutputString
END
GO

No comments: