By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 2,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

concenation of strings and null values

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

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

P: n/a
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.