CODEWITHSUNDEEP
sql-serverisnull
Codeek
Codeek

Reputation: 1624

ISNULL prints *

I am encountering this strange behavior (Okay maybe not strange but beyond my understanding) when using isnull. It all sums up to this:

isnull(left(cast(null as varchar),1),0) gives 0

isnull(left(cast(null as varchar),1),-1) gives *

I would like to know the reason behind this behavior.

Although I got a workaround here: 

select isnull(cast(left(cast(null as varchar),1) as varchar),-1)

Upvotes: 2

Views: 279

Answers (2)

Thom A
Thom A

Reputation: 95561

The reason for the asterisk is due to an overflow error. left(cast(null as varchar),1) would return a varchar(1). Within the ISNULL the value -1 would be implicitly converted to a varchar(1), and a negative number cannot be represented with a single character, hence why an '*' is displayed.

If you change it to a LEFT(...2) then get a result:

SELECT ISNULL(LEFT(CAST(NULL AS varchar),2),-1);

On a different note Bad habits to kick : declaring VARCHAR without (length)

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

You could use COALESCE:

SELECT COALESCE(left(cast(null as varchar),1),-1);

DBFiddle Demo

ISNULL infers data type from first argument, COALESCE infers from wider one(Data type precedence)

Upvotes: 0

Related Questions