ScuzzyJo
asked on
Help with MS SQL Right Function Using the Len Function
Hi
I have a column in a table which is made up of reference numbers following a transaction matching process. It's there to form part of an audit trail. At the moment, it might look something like ", 88654, 12973, 84755". It might also be empty (if no transactions matched). I want to get rid of the comma and space at the beginning. I don't really want to do this as part of the original run as it would slow it down, so thought it would be easiest to do a tidy up afterwards.
The problem is that I can't get the right function to work if I use the len function. I've tried various, but at the moment, I've got:
UPDATE SAS_T_1213_RemAds SET remRef = RIGHT(conCatRem,LEN(conCat Rem)-2) WHERE conCatRem IS NOT NULL;
I get the error message:
Msg 536, Level 16, State 4, Line 5
Invalid length parameter passed to the RIGHT function.
Can anyone help with this please?
Thanks
Sarah
I have a column in a table which is made up of reference numbers following a transaction matching process. It's there to form part of an audit trail. At the moment, it might look something like ", 88654, 12973, 84755". It might also be empty (if no transactions matched). I want to get rid of the comma and space at the beginning. I don't really want to do this as part of the original run as it would slow it down, so thought it would be easiest to do a tidy up afterwards.
The problem is that I can't get the right function to work if I use the len function. I've tried various, but at the moment, I've got:
UPDATE SAS_T_1213_RemAds SET remRef = RIGHT(conCatRem,LEN(conCat
I get the error message:
Msg 536, Level 16, State 4, Line 5
Invalid length parameter passed to the RIGHT function.
Can anyone help with this please?
Thanks
Sarah
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
You need to check the length of conCatRem before passing it to the Right function so that if the Lengh is less then 2 Right function would not get any -Ve Value which is causing error
You need to check the length of conCatRem before passing it to the Right function so that if the Lengh is less then 2 Right function would not get any -Ve Value which is causing error
UPDATE SAS_T_1213_RemAds SET remRef = CASE WHEN LEN(conCatRem) >= 2 THNE (RIGHT(conCatRem,LEN(conCatRem)-2) ELSE conCatRem END WHERE conCatRem IS NOT NULL;
what is the defined length of that column? let's say it is 4000
test it first, always backup...
test it first, always backup...
select
SUBSTRING(remRef ,3, 4000)
from SAS_T_1213_RemAds
where remRef like ' ,%'
;
UPDATE SAS_T_1213_RemAds
SET remRef = SUBSTRING(remRef ,3, 4000)
where remRef like ' ,%'
ASKER
Thanks to everyone who replied. All the answers made sense to me, but Randy got there first and his solution works perfectly :-)
Open in new window