COALESCE returns 8 blank spaces?

Question

COALESCE returns 8 blank spaces?

cvandevoorde

Old Hand

Points: 368
More actions
December 6, 2010 at 9:51 am

#230156

I'm perplexed by a problem I'm having with the COALESCE function. What I'm trying to accomplish is concatenating a name together from several columns (first, middle, last, etc..). The problem is I am getting 8 blank spaces returned when I am trying to get 0 spaces returned. See the comments in the query below.
The same thing happens when I try to use ISNULL(columnname,'') - I get 8 spaces returned. when <columnname> is null.
select cn.PREFIX,cn.FNAME_UPPER,cn.MNAME,cn.BUSINESS_OR_LNAME_UPPER,cn.SUFFIX,
COALESCE(cn.PREFIX + ' ', '') + '*', /* returns '*' when PREFIX null */
COALESCE(cn.PREFIX , '') + '*', /* returns ' *' when PREFIX null, why the spaces?*/
COALESCE(cn.PREFIX + ' ', '') + COALESCE(cn.FNAME_UPPER + ' ', '') + COALESCE(cn.MNAME + ' ', '') + COALESCE(cn.BUSINESS_OR_LNAME_UPPER + ' ', '') + COALESCE(cn.SUFFIX , '') as PAYEE_NAME
from TRN_PAYEE tp
left join CLM_NAME cn on tp.PAYEE_ID = cn.NAME_NID and tp.PAYEE_TYPE = 'NAME'
left join ADM_VENDOR av on tp.PAYEE_ID = av.VENDOR_VID and tp.PAYEE_TYPE in ('VENDOR','VEND2')
where PAYMENT_ID = 2474689
So what I end up with in PAYEE_NAME is something like 'JAMES H WESTBROOK ' when the SUFFIX is null.
Same results using the Isnull() function. Any ideas?

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are the columns varchar or char data types?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

cvandevoorde Old Hand Points: 368 More actions · Answer 2

Huh... The 2 columns I noticed the problem with, the PREFIX and SUFFIX, are char(8). The others are varchar.

I still don't understand why, if the columns contain NULL, I would get a different result from these 2 statements

COALESCE(cn.PREFIX + ' ', '') + '*'

COALESCE(cn.PREFIX , '') + '*'

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

cvandevoorde (12/6/2010)
Huh... The 2 columns I noticed the problem with, the PREFIX and SUFFIX, are char(8). The others are varchar.
I still don't understand why, if the columns contain NULL, I would get a different result from these 2 statements
COALESCE(cn.PREFIX + ' ', '') + '*'
COALESCE(cn.PREFIX , '') + '*'

The ' ' is being translated to a varchar. Char + VarChar = Varchar. It's because you're overriding the setting of the datatype in the first version. An implicit conversion is occurring.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lowell SSC Guru Points: 323518 More actions · Answer 4

cvandevoorde (12/6/2010)
Huh... The 2 columns I noticed the problem with, the PREFIX and SUFFIX, are char(8). The others are varchar.
I still don't understand why, if the columns contain NULL, I would get a different result from these 2 statements
COALESCE(cn.PREFIX + ' ', '') + '*'
COALESCE(cn.PREFIX , '') + '*'

the function has to return a single datatype. like a case statement, you cannot have it return CHAR for the first argument, but varchar for the second or subsequent...so it is doing an implicit conversion to the only known datatype int he statment...whatever the definition of cn.PREFIX is...in this case, a CHAR(8)

you m ight need to do something like this instead:

COALESCE(CONVERT(VARCHAR,cn.PREFIX) + ' ', '') + '*'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cvandevoorde Old Hand Points: 368 More actions · Answer 5

Ok it's all making sense now. Thanks GSquared, Craig, and Lowell for the replies and suggestions.