How to fix an isnull function error in SQL 2016?

Question

What is the correct syntax for the isnull function to avoid the error "The isnull function requires 2 argument(s)" when executing the following line of code in SQL 2016:

TRY_CAST(REPLACE(ISNULL(NULLIF(SUBSTRING(event_id, PATINDEX('%[0-9]%', event_id), 1), '0'), '[^0-9]', ''), '') AS INT) * 10

Thank you.

Answer 1

Hi Clements, Audrey

it looks like you are trying to replace NULL values with an empty string. To correct the syntax, you can use the COALESCE function instead of ISNULL since COALESCE can handle more than two arguments. Here's an updated version of your code:

TRY_CAST(REPLACE(COALESCE(NULLIF(SUBSTRING(event_id, PATINDEX('%[0-9]%', event_id), 1), '0'), ''), '[^0-9]', '') AS INT) * 10

This eliminates the use of ISNULL and instead uses COALESCE for handling NULL values, keeping the expression concise.

If this helps kindly accept the answer thanks much.

Answer 2

isnull takes to arguments, like this:

SELECT isnull(someval, '')

It appears that in your case, you have gone lost in a myriad of nested parentheses, and one of them is in the wrong place. Which one? Oh, I don't really have the time to dig into that now. But this is a battle I also have to fight from time to time. A tip is to break things up on multiple lines. I think that SSMS also has some parentheses-matching function. (Which comes with intellisense, which I just can't stand, so I don't have it turned on myself.)

As for the suggestion of using coalesce, there are some situation where coalesce may fit you better, but this is not one of them. It's just more difficult to spell.