Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LTRIM and RTRIM are not working Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 10:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
SELECT * FROM [dbo].[REPORT_NOTES] AS Rptn

results in one record.

A column called INV_NUM with a value of "998877" and a column called TEXT with a value of "Note information..."

The SSIS package is not picking up the text qualifiers of double quotes on the INV_NUM, but it is removing the double quotes on the TEXT column. They have tasked me with the ability to fix this in the Stored Proc. I have tried all of the below with no luck!

      UPDATE
[dbo].[REPORT_NOTES]
SET
[INV_NUM] = REPLACE([INV_NUM] , '"' , '');

UPDATE
[dbo].[REPORT_NOTES]
SET
[INV_NUM] = LTRIM(RTRIM([dbo].[REPORT_NOTES].[INV_NUM]));

What happens is the LTRIM and RTRIM successfully complete, but the 6 digit INV_NUM is still showing a length of 8 characters. This is a problem since this column is used to join on tables. Since the INV_NUM is 8 characters, SQL can't find the corresponding 6 character INV_NUM in the DB and the process fails.

What can I do to actually get the LTRIM and RTRIM to really work and make that value a real 6 digit actual INV_NUM?

Thank you
Post #1464786
Posted Tuesday, June 18, 2013 10:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
LTRIM and RTRIM remove spaces. If they're not removing your non-printable characters, then they're not spaced. Identify what they are (use the ASCII command) and then use REPLACE or LEFT and RIGHT or SUBSTRING


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1464789
Posted Tuesday, June 18, 2013 10:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:23 PM
Points: 157, Visits: 981
Check the column or parameter definitions for char instead of varchar. Conversions between those are very tricky.
Post #1464791
Posted Tuesday, June 18, 2013 11:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
Thank you Gail.

I realized that the data was working fine before a specific date. After that date, the report loading started to fail. I sent the ticket back to the developer and told him to fix it. I'm not going to BandAid a problem caused by a change without ticket/approval becuase I may break something else downstream.

Thank you for your help.
Post #1464806
Posted Tuesday, June 18, 2013 11:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081


Typically the cause for something like this is non-space whitespace characters. There are a few ascii characters that don't show, but aren't spaces. CR and LF are two that immediately come to mind. Since they aren't spaces, LTRIM and RTRIM won't get rid of them, but they are valid characters and SSIS especially is incredibly picky about strings being absolutely, completely equal. Far more so than SQL Server which is perfectly happy to ignore trailing spaces when comparing stings because it needs to do transparent comparisons (without explicit or implicit conversions) between char and varchar.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1464811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse