471,306 Members | 835 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

concenation of strings and null values

Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

Regards
Tino Wildenhain
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
6 3111
On Tue, 7 Oct 2003, Tino Wildenhain wrote:
Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.
Looks correct to me. Sure, might be annoying but we'd rather have correctness I
think :)

What you're asking for the equivalent of is:

something1 || ???? || something3 to equate to: something1something3

which you obviously can't say in the general case because you don't know if
???? really is an empty string or in fact something much more significant as in
the example:

'do' || 'not' || 'concatinate'

Therefore the whole expression should evaluate to null otherwise you'd never
know you didn't actually know if you should or shouldn't concatinate.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?


In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');
--
Nigel J. Andrews
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2
Hello
SELECT 'abc'::text || null::text;

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.


NULL means here UNKOWN. You cannot add or concatenate something to an
undefined Value.

Regards

Dieter
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #3
Nigel J. Andrews wrote:
On Tue, 7 Oct 2003, Tino Wildenhain wrote:

Hi,

SELECT 'abc'::text || 'def'::text;

returns 'abcdef' as we know.

SELECT 'abc'::text || ''::text;

returns 'abc'

SELECT 'abc'::text || null::text;

returns null

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

Looks correct to me. Sure, might be annoying but we'd rather have correctness I
think :)

What you're asking for the equivalent of is:

something1 || ???? || something3 to equate to: something1something3

which you obviously can't say in the general case because you don't know if
???? really is an empty string or in fact something much more significant as in
the example:


not really, this behavoir is only with null values because all other
values are casted to text. So null (or none, or undefined ...) in most
languages map to "" (Empty string) when concenated.
It may be however the current behavior is according to SQL9[29],
since 1+null is null too (is a case in aggregates, isn't it?)
but I dont know of a human readable reference of the SQL specs.


'do' || 'not' || 'concatinate'

Therefore the whole expression should evaluate to null otherwise you'd never
know you didn't actually know if you should or shouldn't concatinate.

Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?

In this instance use coalesce() as in: SELECT 'abc' || coalesce(null,'');


This works ;) Thanks :-)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4
Tino Wildenhain <ti**@wildenhain.de> writes:
Can someone enlighten me if this is
in accordance to some not so transparent
rules of SQL92 or '99 and if so, how
to work around this?


It is per spec: in SQL92 6.13 <string value expression>:

2) If <concatenation> is specified, then let S1 and S2 be the re-
sult of the <character value expression> and <character factor>,
respectively.

Case:

a) If either S1 or S2 is the null value, then the result of the
<concatenation> is the null value.
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #5

Tino Wildenhain <ti**@wildenhain.de> writes:
So null (or none, or undefined ...) in most
languages map to "" (Empty string)


Not in SQL. In SQL null means "unknown". If it's unknown then it's still
unknown when there's something attached to the beginning of it.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #6
Dieter Fischer (grid-it) wrote:
Hello

SELECT 'abc'::text || null::text;

The last example looks like a bug,
but if it is intentionally so, its
at least very annoying and inconvenient.

NULL means here UNKOWN. You cannot add or concatenate something to an
undefined Value.

You can but the result is UNKNOWN.

Regards
Gaetano Mendola

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Stefan Weiss | last post: by
16 posts views Thread by Paul Prescod | last post: by
4 posts views Thread by Barry | last post: by
4 posts views Thread by Randall Parker | last post: by
1 post views Thread by Dave | last post: by
18 posts views Thread by Pedro Pinto | last post: by
1 post views Thread by archanapatelwhite | last post: by
15 posts views Thread by Szabolcs | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.