RSS

Tracking Down Ghost Characters: WHERE Clause Does Not Work Properly In T-SQL Query

25 Jan

Hi guys,
I would like to share an experience with you when I was working on 1 SSIS related issue.

Scenario was:
I have to exclude some records from Table A by matching some keys of Table B.

 Table A

Year Month CustomerCode <Extra Columns>
2012 JAN CX1000  
2012 JAN CX2000  
2012 JAN CD3000  

 

 Table B

Year Month RecCustomerCode <Extra Columns>
2012 JAN CX1000  
2012 JAN CX7000  
2012 JAN CD8000  

 

 What is expected was – Table A’s following records:

Year Month CustomerCode <Extra Columns>
2012 JAN CX2000  
2012 JAN CD3000  

 

 So, only records of Table A should be used which are not matching with Table B.
It was a simple correlated T-SQL query. But I was facing some issue.

What was happening:

When I simply query following, I could see results. I mean results, where CustomerCode is ‘CX1000′
Select * From Table A;

When I do following query, I do not get any results. This was very surprised for me.
Select * From Table A Where CustomerCode = ‘CX1000′;

Then I tried following query, and I found required results.
Select * From Table A Where CustomerCode like ‘%CX1000%’;

Then I suspect something hidden/not visible information after ‘CX1000′ value. and I tried following to confirm. and I got results. And there I found my Ghost :)
Select * From Table A Where CustomerCode like ‘%CX1000%’ + Char(13);

The issue was:
Table A was filled by an SSIS package from some CSV file. and this column (i.e. CustomerCode) was at the last position of the column row in file. So while loading of this Table A, carriage return was inserted in the last column :) How … is not our concern right now.

What you should do if you encounter this issue:
1. First thing you should do is to fix your SSIS package. it should insert only values and of proper length and type.
2. If fixing SSIS is not possible or permitted (may be its not in our hands), then you should remove unnecessary characters from that column value which querying that Table. E.g. LEFT(LTRIM(RTRIM(CUSTOMERCODE)), 6)
3. If you want, you can find Char(13) or Char(10) values in your columns and update whole table by removing them. (I will post very good script for that later…)

Note: I would like to share a credit for this post with my good friend & colleague Santosh Joshi (His blog: http://microsoftbizintel.wordpress.com).

I hope this will help you somewhere.
Thanks, Khilit

 

Tags: , , , , , , , , ,

Leave a Reply

 
%d bloggers like this: