Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LTRIM and RTRIM are not working


LTRIM and RTRIM are not working

Author
Message
SQL_Enthusiast
SQL_Enthusiast
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 422
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
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, MVP, M.Sc (Comp Sci)
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


Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1817
Check the column or parameter definitions for char instead of varchar. Conversions between those are very tricky.
SQL_Enthusiast
SQL_Enthusiast
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 422
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
:-D

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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search