Overcoming SQL's lack of short-circuit of AND conditions

Question

I'm processing an audit log, and want to ignore entries where a NULL value is changed to zero (or remains NULL). The Old and New values are held in NVARCHAR fields regardless of the type of the fields being logged. In order to CAST a new value to decimal, to determine if it's zero, I need to restrict to cases where ISNUMERIC of the field returns 1.

I've got it to work with this strange bit of SQL - but feel sure there must be a better way.

WHERE MessageDesc LIKE 'poitem%'
AND NOT(OldValue IS NULL AND 0.0 =
    CASE
        WHEN ISNUMERIC(NewValue) = 1 THEN CAST(NewValue AS DECIMAL(18,4))
        WHEN NewValue IS NULL THEN 0.0
        ELSE 1.0
    END)

Any suggestions?

score 1 · Answer 1 · edited May 23 '17 at 10:25

SQL Server 2012 added a Try_Convert function, which returns NULL if the value cannot be casted as the given type. http://technet.microsoft.com/en-us/library/hh230993.aspx

WHERE NOT (OldValue is Null AND 
    (NewValue is null OR try_convert(float, NewValue) = 0.0)
)

~~If using a version prior to 2012, check out Damien_The_Unbeliever's answer here: Check if a varchar is a number (TSQL)~~ ...based on Aaron's comment this will not work in all cases.

Since you are using SQL 2008, then it appears a combination of isnumeric and a modified version of Damien's answer from the link above will work. Your current solution in your question would have problems with values like '.', '-', currency symbols ($, etc.), and scientific notation like '1e4'.

Try this for SQL 2008 (here is SQLFiddle with test cases: http://sqlfiddle.com/#!3/fc838/3 ): Note: this solution will not convert text values to numeric if the text has commas (ex: 1,000) or accounting notation with parens (ex: using "(1)" to represent "-1"), because SQL Server will throw an error when trying to cast to decimal.

WHERE t.OldValue is null
AND 
(
    t.NewValue is null
    OR
    0.0 = 
    case 
        when isnumeric(t.NewValue) = 1 
            --Contains only characters that are numeric, negative sign, or decimal place. 
            --This is b/c isnumeric will return true for currency symbols, scientific notation, or '.'
            and not (t.NewValue like '%[^0-9.\-\+]%' escape '\')  
            --Not other single char values where isnumeric returns true.
            and t.NewValue not in ( '.', '-', '+')
        then cast(t.NewValue as decimal(18,4)) 
        else null --can't convert to a decimal type 
    end
)

score 0 · Answer 2 · answered Jan 27 '14 at 16:49

Avoid ISNUMERIC() since it is problematic with '.'.

-- Dot comes up as valid numeric
select 
  ISNUMERIC('.') as test1,
  ISNUMERIC('1A') as test2,
  ISNUMERIC('1') as test3,
  ISNUMERIC('A1') as test4

-- Results window (text)
test1       test2       test3       test4
----------- ----------- ----------- -----------
1           0           1           0

Use COALESCE() instead.

WHERE MessageDesc LIKE 'poitem%'
AND 
    NOT (OldValue IS NULL AND 
    CAST(COALESCE(NewValue, '0') AS DECIMAL(18,4)) = 0)

This doesn't seem to work when NewValue is something that would produce an "Error converting data type varchar to numeric." - though you're right that a single "." causes my code to error as well. — JakeA, Jan 29 '14 at 09:45

Overcoming SQL's lack of short-circuit of AND conditions

2 Answers2