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)
)
RETURNS NVARCHAR(500)
AS
BEGIN
-- 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

END





Advertisements

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