Replace/Remove Special Characters in Sql Server 2005/2008

   Tweet

Sql Server dosen't have any inbuilt function for replacing special characters. We can create a function to replace/remove special characters. Sql Function to replace special characters is follows

CREATE FUNCTION dbo.ReplaceSpecialChars (@inpStr VARCHAR(512),@repChar CHAR(1))
RETURNS VARCHAR(512)
BEGIN
	DECLARE @retStr VARCHAR(512)
	SET @retStr = ''
	IF(LEN(@inpStr) > 0)
	BEGIN
		DECLARE @l INT
		SET @l = LEN(@inpStr)
		DECLARE @p INT
		SET @p = 1
		WHILE @p <= @l
		BEGIN
			DECLARE @c INT
			SET @c = ASCII(SUBSTRING(@inpStr, @p, 1))
			IF @c BETWEEN 48 AND 57 OR @c BETWEEN 65 AND 90 OR @c BETWEEN 97 AND 122
				SET @retStr = @retStr + CHAR(@c)
			ELSE
				SET @retStr = @retStr + @repChar
				SET @p = @p + 1
		END
	END
RETURN @retStr
END

To replace special characters with ‘-’ use the below query

Select dbo.ReplaceSpecialChars(’English,Tamil,Hindi Movies’,'-’)

Output : English-Tamil-Hindi-Movies

To remove special characters use following query

Select dbo.ReplaceSpecialChars(’English,Tamil,Hindi Movies’,”)

Output : EnglishTamilHindiMovies

Code Sample to replace continues replace chars:

If you have continues special character, you will get many replace characters. to remove multiple replace character use below code before the return statement.

while charindex(@repChar+@repChar, @retStr ) > 0 
begin 
select @retStr = Replace( @retStr , @repChar+@repChar,@repChar) end

Select dbo.ReplaceSpecialChars(’English , Tamil , Hindi Movies’,'-’)

Output : “English-Tamil-Hindi-Movies” instead of “English—Tamil—Hindi-Movies”

   Tweet