TSQL Script to remove TAB, LINE FEED and CARRIAGE RETURN

The below script removes the TAB(Horozontal Tab), Line feed(New line), Carriage Return Characters in a variable @String

[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
[/sql]

Extended script which also provides length of the string:
[sql]
SET NOCOUNT ON
DECLARE @String VARCHAR(100)
DECLARE @CorrectedString VARCHAR(100)

SELECT @String = ‘AB C
D’

PRINT @String
PRINT ‘LENGTH=’+CAST(LEN(@String) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@String, CHAR(9),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(10),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))

SELECT @CorrectedString = REPLACE(@CorrectedString, CHAR(13),”)
PRINT @CorrectedString
PRINT ‘LENGTH=’+CAST(LEN(@CorrectedString) AS VARCHAR(5))
[/sql]

Leave a Reply

Your email address will not be published. Required fields are marked *