April 17, 2012 at 6:39 am
Hi Friends,
I have a scenario of using look up task in my DF. when i joined an input column to a look up column, i got the exact rows what i was looking for....
Here is my doubt, In a while before i was trimming the same input column in a derived column task and joined it to a look up column, but i did not get any rows due to trimming...
i tried to test this using SQL statements like this 'Select column from table1 as a join table2 as b on LTRIM(RTRIM(a.column) = b.column'
this query outputs the exact results...but the same scenario is not working in ssis... i hope SQL is not a sensitive for white spaces in back of the values but in front is sensitive....
sorry if am wrong...
Any suggestions would be really appreciated....
Thanks,
Charmer
April 18, 2012 at 12:08 am
SQL Server itself is normally insensitive to white space. However, the SSIS lookup isn't.
If you use TRIM, you need to use it on the source data and on the reference data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 12:14 am
I've seen different results when using trim in SSIS compared to trimming in a SQL statement.
As a matter of course now I use a script component to do my trimming with a .net function.
I suspected at the time that the SSIS trim was very specific in the 'type' of whitespace it trims, not sure how correct this was but the .net function works correctly!
April 19, 2012 at 12:20 am
sam.dahl (4/19/2012)
I've seen different results when using trim in SSIS compared to trimming in a SQL statement.As a matter of course now I use a script component to do my trimming with a .net function.
I suspected at the time that the SSIS trim was very specific in the 'type' of whitespace it trims, not sure how correct this was but the .net function works correctly!
According to BOL:
TRIM does not remove white-space characters such as the tab or line feed characters
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 12:23 am
hmm, maybe I was encountering tab characters. If the SQL trim removes tabs that would be consistent with what I saw.
April 19, 2012 at 12:25 am
sam.dahl (4/19/2012)
hmm, maybe I was encountering tab characters. If the SQL trim removes tabs that would be consistent with what I saw.
The problem is that MSDN is not really explicit about the TSQL LTRIM and RTRIM. It just says "blanks". Whatever that is.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 2:28 am
Koen Verbeeck (4/19/2012)
sam.dahl (4/19/2012)
hmm, maybe I was encountering tab characters. If the SQL trim removes tabs that would be consistent with what I saw.The problem is that MSDN is not really explicit about the TSQL LTRIM and RTRIM. It just says "blanks". Whatever that is.
I have a doubt Koen...
If we use Trim's in TSQL in join conditions, would it be a performance issue?
Thanks,
Charmer
April 19, 2012 at 2:48 am
Charmer (4/19/2012)
Koen Verbeeck (4/19/2012)
sam.dahl (4/19/2012)
hmm, maybe I was encountering tab characters. If the SQL trim removes tabs that would be consistent with what I saw.The problem is that MSDN is not really explicit about the TSQL LTRIM and RTRIM. It just says "blanks". Whatever that is.
I have a doubt Koen...
If we use Trim's in TSQL in join conditions, would it be a performance issue?
I'm not really sure. Maybe the function prevents the optimizer from using indexes (as it does if you are not using SARGs in the WHERE clause). The default setting of SQL Server ignores white spaces, so why would you do a TRIM? Also, it would be more efficient to join on an integer column instead of on a string column.
Why don't you try it out? Execute both queries, one with TRIM, one without, and compare the actual execution plans.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 3:23 am
I'm not really sure. Maybe the function prevents the optimizer from using indexes (as it does if you are not using SARGs in the WHERE clause). The default setting of SQL Server ignores white spaces, so why would you do a TRIM? Also, it would be more efficient to join on an integer column instead of on a string column.
Why don't you try it out? Execute both queries, one with TRIM, one without, and compare the actual execution plans.
The reason i use trim is , two columns are string having values of integers with one string character(ex: A11500)....one column is defined as char and another one is varchar data type...
the problem is with char data type since as you know,char allocates spaces if the exact values is less than the column length...
So i have to use trim's to join on two columns...do i really need it or not?
And Koen, what is SARGs? Could you explain about it ?
Thanks,
Charmer
April 19, 2012 at 3:33 am
It's not really efficient to join on two different datatypes.
Ideally you would convert to char column to varchar or vice versa.
The behaviour of SQL Server and white space can depend on some settings, such as ANSI_PADDING.
Run the query with and without trim and see if you get different results.
Regarding SARG: a quick Google search won't kill you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2012 at 4:03 am
Yeah, i get the same result with trim and without trim...
but some times i see the columns have spaces in front of the values.....in this case i hope i need to use trim...don't i ?
Thanks,
Charmer
April 19, 2012 at 1:35 pm
Charmer (4/19/2012)
Yeah, i get the same result with trim and without trim...but some times i see the columns have spaces in front of the values.....in this case i hope i need to use trim...don't i ?
IF('test' = ' test')
PRINT 'They are equal';
ELSE
PRINT 'They are NOT equal';
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 20, 2012 at 12:39 pm
He's not returned. You don't suppose that a Google search DID kill him? Yikes. Bring back AltaVista, it never killed anyone!
April 21, 2012 at 1:31 am
herladygeekedness (4/20/2012)
He's not returned. You don't suppose that a Google search DID kill him? Yikes. Bring back AltaVista, it never killed anyone!
I am back..:-)
Thanks,
Charmer
April 23, 2012 at 10:47 am
Whew, thanks for reporting back! wasn't certain that Google's newest privacy policy would let you back out!! :hehe:
Seriously, tho, I have a string-compare issue myself now also so was following your progress because it made more sense than fuming and pulling my hair out (misery loves company, as the saying goes).
My compare values both are varchar(50) at input so not quite the same issue.
I also am using a custom lookup component but had thought about using the MS one. Not optimistic that it will get me around the issue...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply