SQL Server 2008 R2 – RIGHT() Works in SELECT but Not in UPDATE

sql serversql-server-2008-r2t-sql

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,  
        A.MinRelease,
        A.Build,
        Split.a.value('.', 'VARCHAR(MAX)') AS ReleaseNotes
INTO #ReleaseTemp
FROM  
        (
            SELECT  MajRelease,
                    MinRelease,
                    Build,  
                    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,
        MinRelease,
        Build,
        RIGHT(ReleaseNotes, LEN(ReleaseNotes) - 2) AS ReleaseNots
FROM #ReleaseTemp
WHERE #ReleaseTemp.ReleaseNotes <> '';

Best Answer

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.

Related Question