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