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 = ‘ABC
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 = ‘ABC
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]