469,266 Members | 1,768 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

Return Type of COALESCE?

Hi everybody,

VARCHAR has a higher precedence than CHAR. If you have a query

SELECT COALESCE(c1,c2) FROM T1

where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the return
type to be varchar(10) similiar to UNION. However it turns out to
be CHAR(10). Does anyone know why that is so?

Thanks,
Steffen
Jul 23 '05 #1
3 9031
I'm not sure why you are seeing this behavior. According to BoL
COALESCE acts just like a CASE statement. According to the
documentation there the precedence order is supposed to be followed for
data type conversions. Yet, when I run the code below I get
contradictory results:

DECLARE @test INT, @char CHAR(10), @varchar VARCHAR(10)
SET @test = 1
SET @char = 'char'
SET @varchar = 'varchar'
SELECT 'x' + CASE @test WHEN 1 THEN @varchar ELSE @char END + 'x'

I ran this on a system with ANSI_PADDING set to ON and it had spaces
between the string and the final 'x'. If I make both arguments VARCHAR
the trailing spaces are not there.

When I tested this with an INT and DECIMAL I saw the results that I
would expect there - the result is always returned as a DECIMAL.

-Tom.

Jul 23 '05 #2
COALESCE() is defined in terms of a CASE expression, so it should
follow the same rules about returning the highest data type in the
list. COALESCE correctly promotes its arguments to the highest data
type in the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

It sounds like the compiler is giving you an ISNULL().

Jul 23 '05 #3
--CELKO-- wrote:
COALESCE() is defined in terms of a CASE expression, so it should
follow the same rules about returning the highest data type in the
list. COALESCE correctly promotes its arguments to the highest data
type in the expression:

13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5

The proprietary ISNULL() uses the first data type and gets things wrong

13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6

You would need to write:

13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)

It sounds like the compiler is giving you an ISNULL().

I dont think so.
SELECT COALESCE(CAST (NULL AS CHAR(5)), CAST('a' as VARCHAR(10)) returns
'a ', a fixed length char.
SELECT COALESCE(CAST('a' as VARCHAR(10), CAST (NULL AS CHAR(5))) returns
the same. The order of the arguments doesnt change anything.
Only if all arguments are varchar, do I get varchar back.

Regards,
Steffen
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Bob Bedford | last post: by
2 posts views Thread by Jeff Roughgarden | last post: by
5 posts views Thread by John Greve | last post: by
7 posts views Thread by KevinMGore | last post: by
10 posts views Thread by db2group88 | last post: by
4 posts views Thread by lenygold via DBMonster.com | last post: by
1 post views Thread by =?Utf-8?B?R1ROMTcwNzc3?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.