Remove Special Characters from a string in SQL Server

Here is a sql script which I found on some forum while searching for the same which I thought is interesting enough to share. This script can be used to remove special characters from a string in SQL Server –

/*********************************
Removes any special characters from 
@inputString that do not meet the 
provided criteria.
*********************************/
CREATE FUNCTION [dbo].[udfGetCharacters](@inputString VARCHAR(MAX), @validChars VARCHAR(100))
RETURNS VARCHAR(500) AS
BEGIN
 
	WHILE @inputString like '%[^' + @validChars + ']%'
		SELECT @inputString = REPLACE(@inputString,SUBSTRING(@inputString,PATINDEX('%[^' + @validChars + ']%',@inputString),1),'')

	RETURN @inputString
END

This function takes input string and the valid characters and replaces all the characters other than those in valid characters string. This function can be used as follows–

--Usage of the function
select [dbo].udfGetCharacters('utkarsh puranik`s blog' ,'0-9a-z ')

--output
utkarsh puraniks blog
Advertisements

About Utkarsh Puranik

Software Engineer by Profession, Gamer by Nature, Techy by Attitude and a Good Person at Heart
This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

3 Responses to Remove Special Characters from a string in SQL Server

  1. mangesh says:

    Hi Utkarsh,
    Thanks for this wonderful function
    it helped me a lot

  2. Aqil Ahmed says:

    Utraksh,

    Your function helped me a lot.

    Thanks for sharing it.

    Thanks
    Aqil

  3. Ico says:

    Thank you man! It works like a charm!

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