What will LTRIM and RTRIM do with CR LF or CRLF?
IF OBJECT_ID('tempdb.dbo.[#testtable]') IS NOT NULL
DROP TABLE [#testtable]
CREATE TABLE [#testtable] ([TestCRLF] VARCHAR(50))
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13) + CHAR(10)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(10)
)
SELECT [TestCRLF] + 'Z' AS NoPostProcessing
FROM [#testtable]
SELECT LTRIM(RTRIM([TestCRLF])) + 'Z' AS Trims
FROM [#testtable]
SELECT RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([TestCRLF], CHAR(13) + CHAR(10), ' '), CHAR(10) + CHAR(13), ' '), CHAR(13), ' '),
CHAR(10), ' ')) + 'Z' ReplaceAndTrim
FROM [#testtable]
Here’s what the output looks like:
Of course if we output to text the Z character would be in the next line. Since we are trying to clean up data with spaces or CRLF this shows a good demonstration of the problem.
(Thanks to Chris Trump!)