Why would this return a single space instead of two spaces?
select isnull(nullif('',''),' ')
June 28, 2021 at 2:11 pm
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.
June 28, 2021 at 2:46 pm
Why not something like this?
IIF(LEN(Col1) <2, Col1, LEFT(Col1, LEN(Col1) - 2))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
June 28, 2021 at 2:57 pm
Solution:
Use COALESCE instead of ISNULL, i.e.:
select COALESCE(nullif('',''),' ')
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 28, 2021 at 3:04 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply