isnull returns wrong number of spaces (i think)

Question

isnull returns wrong number of spaces (i think)

Jackie Lowery

SSCommitted

Points: 1889
More actions
June 28, 2021 at 2:08 pm
Go to Answer
#3901209
Why would this return a single space instead of two spaces?
```
select isnull(nullif('',''),'  ')
```
Thom A

SSC Guru

Points: 99128
More actions
June 28, 2021 at 2:52 pm
Answer
#3901230

ISNULL returns the data type of the first parameter.The first parameter of your ISNULL function is NULLIF('','') which if we use dm_exec_describe_first_result_set we can see returns a varchar(1). As a result the second parameter is converted from a varchar(2) to a varchar(1), and thus you get ' ' not ' ' as the value is truncated.
If you want to return ' ' then use COALESCE, which is a short hand CASE expression. This uses Data Type Precedence to determine the return data type, meaning that a varchar(2) would be returned, not a varchar(1): COALESCE(NULLIF('',''),' ')
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 1

I guess i should say the goal here is to do a :

left(column1,len(column1)-2)

and I'm trying to account for Null or Empty fields so the left function doesn't give me an error.

Phil Parkin SSC Guru Points: 246955 More actions · Answer 2

Why not something like this?

IIF(LEN(Col1) <2, Col1, LEFT(Col1, LEN(Col1) - 2))

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101085 More actions · Answer 3

Solution:

Use COALESCE instead of ISNULL, i.e.:

select COALESCE(nullif('',''),' ')

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 4

Thanks guys. The coalesce solution is what I needed. Phils solution would work, except I want to return an empty string if there's less than 2 characters.