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!
[INV_NUM] = REPLACE([INV_NUM] , '"' , '');
[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?