I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use it in a SELECT..FROM but when I use that same code inside an UPDATE it fails with

Invalid length parameter passed to the RIGHT function

why is it failing in the UPDATE but not the SELECT...FROM?

USE JobSight;

DROP TABLE #ReleaseTemp;

SELECT  A.MajRelease,  
        Split.a.value('.', 'VARCHAR(MAX)') AS ReleaseNotes
INTO #ReleaseTemp
            SELECT  MajRelease,
                    CAST ('<M>' + REPLACE(RelNotes, ';;', '</M><M>') + '</M>' AS XML) AS ReleaseNotes
            FROM JobsDB.dbo.ReleaseData
        ) AS A 
    CROSS APPLY ReleaseNotes.nodes ('/M') AS Split(a);

UPDATE #ReleaseTemp
SET ReleaseNotes = RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2);

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(#ReleaseTemp.ReleaseNotes);

SELECT  MajRelease,
        RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2) AS ReleaseNots
FROM #ReleaseTemp
WHERE #ReleaseTemp.ReleaseNotes <> '';

why is it failing in the UPDATE but not the SELECT...FROM?

Because in your SELECT you are filtering out the rows via WHERE #ReleaseTemp.ReleaseNotes <> ''. You do not have that filter on your UPDATE statement and you probably have at least one row that has a length of 0 or 1. Try making it:

UPDATE #ReleaseTemp
SET ReleaseNotes = LTRIM(RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2))
WHERE LEN(#ReleaseTemp.ReleaseNotes) > 1;

Rather than checking for <> '', I changed the filter to ensure that the length of the string is at least 2 characters. This avoids the situation in which the string has only one character which does not equate to an empty string '' but would still get that error when passing in a -1 (from length of 1 minus 2) to the RIGHT function.

Please note that I included the LTRIM() which you currently have as a second step in order to avoid that second DML operation.


To do updates with select and avoid ambiguity, I use below approach which I learned, hope this helps you too.This is nowhere related to your question.

Small test:

create table #temp
id int
) ;

insert into #temp
select 1 ;

-- now update part

update a
set a.id=3
from #temp a
where a.id=3 ;

---- using cte, I can check the output as well and also it's readable
with cte
select * from #temp where id=1
update cte
set id=2 ;

The 'cte' referred to in the code is a Common Table Expression.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.