December 6, 2006 at 11:47 am
Hi, I have 2 versions of the same table in different databases. In DatabaseA, TableA has some imbedded CR-LF in various positions. In DatabaseB, TableB has the CR-LF already removed. Now I want to compare the tables and identify those records that have differences in the text, but ignore the CR-LF differences.
In other words I want to compare data in ColumnA and ignore the CR-LF so that I consider the first set the same. TRIM would work for blanks, but BOL says not for CR-LF type situations. Any Ideas ? I can't actually update TableA to remove the CR-LF as was done on TableB
ColumnA
TableA "Sent Invoice {CRLF} yesterday"
TableB "Sent Invoice yesterday"
And consider these different
TableA "Sent Invoice yesterday"
TableB "Sent Invoice Dec 5"
December 6, 2006 at 12:04 pm
Use Replace() to strip out the CRLF pairs prior to comparing.
Declare @CRLF As char(2)
-- Construct a CR/LF character pair
Select @CRLF = char(13) + Char(10)
Select *
From TableA
Inner Join TableB
On (TableB.ColumnA = Replace(TableA.ColumnA, @CRLF, ''))
December 6, 2006 at 12:09 pm
Use the REPLACE function. Something like:
SELECT *
FROM TableA A
JOIN TableB B ON A.PK = B.PK
WHERE REPLACE(REPLACE(A.ColWithCRLF, CHAR(13), ''), CHAR(10), '') <> B.ColWithoutCRLF
December 6, 2006 at 12:21 pm
Replace did the trick. Thanks to both of you !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply