T-SQL: How to remove ‘special’ characters from a string?

I’ve written a handy scalar-valued function to remove any ‘special’ characters from an input-string, such as:

Tab -> CHAR(9)
Line Feed (LF) -> CHAR(10)
Carriage Return (CR) -> CHAR(13)

-- =============================================
-- Author: Clint Huijbers
-- Create date: 20130930
-- Description: I've created this SVF to remove all 'special characters' (e.g. CR, LF, TAB, etc) from a string.
-- =============================================
CREATE FUNCTION [dwh].[svf_RemoveSpecialCharacters]
@InputString NVARCHAR(500)
-- Declare the return variable here
DECLARE @ReturnString NVARCHAR(500)

Tab -> CHAR(9)
Line feed -> CHAR(10)
Carriage return -> CHAR(13)

SET @ReturnString = REPLACE(REPLACE(REPLACE(ISNULL(@InputString,''),CHAR(9),''),CHAR(10),''),CHAR(13),'')

-- Return the result of the function
RETURN @ReturnString


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 )

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