SQL – Remove special Characters from string

I had a requirement to eliminate all the special characters except alphabets and numbers. The traditional way to handle it REPLACE( REPLACE( ‘#My &String’, ‘!’, ” ), ‘#’, ” ) was not helping as I need to list all possible special characters… So, created a function to eliminate all special characters from the string;

-- Traditional way to remove specific characters 
SELECT REPLACE( REPLACE( REPLACE( REPLACE( '#My &String', '!', '' ), '#', '' ), '$', '' ), '&', '' );

-- This function eliminates all special characters except alphabets and numerical values
IF OBJECT_ID (N'dbo.fn_eliminatespecialchars', N'FN') IS NOT NULL
    DROP FUNCTION mystring;
GO
CREATE FUNCTION dbo.fn_eliminatespecialchars(@string nvarchar(250))
RETURNS nvarchar(250)
AS
BEGIN
	DECLARE @returnstring nvarchar(25)
	declare @i int, @j int, @m int

	set @returnstring = @string
	set @i = 25

	set @j = PATINDEX('%[^a-z0-9]%',@returnstring)
	if (@j = 0)
		set @i = @j

	while (@i > 0)
	begin
		set @m = len(@returnstring)
		set @returnstring = SUBSTRING(@returnstring,1,@j-1)+SUBSTRING(@returnstring,@j+1,@m)
		set @j = PATINDEX('%[^a-z0-9]%',@returnstring)
		if (@j = 0)
			set @i = @j
		else
			set @i = @i - 1 
	end
	RETURN(@returnstring);
END;
GO

-- Recursive Function to Remove all special characters in a string 
IF OBJECT_ID (N'dbo.fn_eliminatespecialchars_rec', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_eliminatespecialchars_rec;
GO
CREATE FUNCTION dbo.fn_eliminatespecialchars_rec(@string nvarchar(125))
RETURNS nvarchar(125)
AS
BEGIN
	if (PATINDEX('%[^a-z0-9]%',@string) = 0)
		return @string
	else 
		return dbo.fn_eliminatespecialchars_rec(SUBSTRING(@string,1,PATINDEX('%[^a-z0-9]%',@string)-1)+SUBSTRING(@string,PATINDEX('%[^a-z0-9]%',@string)+1,len(@string)))
	return @string;
END;
GO

My next requirement is to eliminate special characters like ë, make my search Kana sensitive. I updated my function as described below using Collation;

IF OBJECT_ID (N'dbo.fn_eliminatespecialchars', N'FN') IS NOT NULL
    DROP FUNCTION fn_eliminatespecialchars;
GO
CREATE FUNCTION dbo.fn_eliminatespecialchars(@string nvarchar(250))
RETURNS nvarchar(250)  -- COLLATE SQL_Latin1_General_CP1_CI_AS
AS
BEGIN
       DECLARE @returnstring nvarchar(25) 
       declare @i int, @j int, @m int
 
       set @returnstring = @string   
       set @i = 25
 
		set @j = PATINDEX('%[^abcdefghijklmnopqrstuvwxyz0123456789]%',@returnstring collate Latin1_General_CI_AS_KS) 
       if (@j = 0)
              set @i = @j
 
       while (@i > 0)
       begin
              set @m = len(@returnstring)
              set @returnstring = SUBSTRING(@returnstring,1,@j-1)+SUBSTRING(@returnstring,@j+1,@m)
              set @j = PATINDEX('%[^abcdefghijklmnopqrstuvwxyz0123456789]%',@returnstring collate Latin1_General_CI_AS_KS)
              if (@j = 0)
                     set @i = @j
              else
                     set @i = @i - 1 
       end
       RETURN(@returnstring);
END;
GO

Improved version 🙂

IF OBJECT_ID (N'dbo.fn_eliminatespecialchars', N'FN') IS NOT NULL
    DROP FUNCTION fn_eliminatespecialchars;
GO
CREATE FUNCTION dbo.fn_eliminatespecialchars(@string nvarchar(250))
RETURNS nvarchar(250)
AS
BEGIN
       declare @i int
  
        set @i = PATINDEX('%[^abcdefghijklmnopqrstuvwxyz0123456789]%',@string collate Latin1_General_CI_AS_KS) 
       while (@i > 0)
       begin
			SELECT @string = STUFF(@string,@i,1,'')
            set @i = PATINDEX('%[^abcdefghijklmnopqrstuvwxyz0123456789]%',@string collate Latin1_General_CI_AS_KS)
       end
       RETURN(@string);
END;
GO
Advertisements
This entry was posted in Script, SQL, SQL Query, Uncategorized and tagged , , , , , , , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s