Question: 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 <> '';

```

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:

```sql
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
as
(
select * from #temp where id=1
)
update cte
set id=2 ;

```

The 'cte' referred to in the code is a [Common Table Expression](https://msdn.microsoft.com/en-us/library/ms175972.aspx).