Removing Duplicate Data from SQL Query Caused by New Line Character

No Comments

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;' )
Categories: SQL Tags: Tags: