CODEWITHSUNDEEP
sql-servert-sql
Fourat
Fourat

Reputation: 2447

tsql Avoid errors in where clause

I have a query like this (SQL server):

UPDATE TableX set FieldB = 'SomeValue'  
where
FieldA is not null and FieldC is not NULL and FieldB is null and FieldD > 0 and
substring(FieldA,5, CHARINDEX('/', FieldA, 5) - 5) in ('ValueA','ValueB')

The problem is that sometimes FieldA is null and this query fails on the first occurrence of null on FieldA, I don't want that.

The error message is:

Invalid length parameter passed to the LEFT or SUBSTRING function.

And it caused by FieldA being null.

Please help.

Upvotes: 2

Views: 188

Answers (2)

Rich Benner
Rich Benner

Reputation: 8113

I'd wrap that final clause in parentheses and allow for the value to be NULL, something like this;

UPDATE TableX 
set FieldB = 'SomeValue'  
where
FieldA is not null 
and FieldC is not NULL 
and FieldB is null 
and FieldD > 0 
and
(
substring(FieldA,5, CHARINDEX('/', FieldA, 5) - 5) in ('ValueA','ValueB')
OR
FieldA IS NULL
)

If you're still having issues with NULL then it may be because your CHARINDEX can possibly return a negative number (if it doesn't find a '/' in your data). Provide some sample data and you'll get additional answers. Try the query below which uses the ABS() function;

UPDATE TableX 
set FieldB = 'SomeValue'  
where
FieldA is not null 
and FieldC is not NULL 
and FieldB is null 
and FieldD > 0 
and
(
substring(FieldA,5, ABS(CHARINDEX('/', ISNULL(FieldA,''), 5) - 5)) in ('ValueA','ValueB')
OR
FieldA IS NULL
)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

One option would be to coalesce FieldA to a dummy string in the event that it be NULL:

UPDATE TableX
SET FieldB = 'SomeValue'  
WHERE FieldA IS NOT NULL AND
      FieldC IS NOT NULL AND
      FieldB IS NULL AND
      FieldD > 0 AND
      SUBSTRING(COALESCE(FieldA, '          /'),
                5,
                CHARINDEX('/', COALESCE(FieldA, '          /'), 5) - 5) 
          IN ('ValueA','ValueB')

The dummy string I used is ten spaces followed by a forward slash, i.e. __________/

Now when FieldA be NULL either the IS NULL check will be hit first, or the substring condition will be hit first. In both cases, the WHERE check would fail for NULL FieldA.

Upvotes: 2

Related Questions