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))
	WHILE @inputString like '%[^' + @validChars + ']%'
		SELECT @inputString = REPLACE(@inputString,SUBSTRING(@inputString,PATINDEX('%[^' + @validChars + ']%',@inputString),1),'')

	RETURN @inputString

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 ')

utkarsh puraniks blog

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:


    Your function helped me a lot.

    Thanks for sharing it.


  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: Logo

You are commenting using your 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