Removing Duplicate Data from SQL Query Caused by New Line Character

We had a query retrieving data from a linked Oracle server. We needed unique rows only. This is the original query:

SELECT Column
     FROM OPENQUERY( LinkedServer, 'SELECT DISTINCT Column from TABLE;' );

However, this still returned some duplicates despite using DISTINCT. As it turned out, some rows had a new line character in them. The solution:

SELECT distinct replace(replace(Column,CHAR(13),''),CHAR(10),'')
     FROM OPENQUERY( LinkedServer, 'SELECT DISTINCT Column from TABLE;' )

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.