[SQL] How concat 3 strings if 2 are not empty?
Hi, I'd like a function that concats 3 string parameters on condition the 1st and 3rd are NOT empty or NULL. xCat (s1, s2, s3) s2 would be a connector that appears only if s1 and s3 are set. NULL and an empty string '' should be handled the same. e.g. 'PostgreSQL', ' is ', ' great'--> 'PostgreSQL is great' NULL, ' is ', ' great' --> 'great' 'PostgreSQL', ' is ', NULL--> 'PostgreSQL' NULL, ' is ', NULL --> NULL 'PostgreSQL', NULL, ' great'--> 'PostgreSQL great' Regards Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How concat 3 strings if 2 are not empty?
In response to Andreas : > Hi, > > I'd like a function that concats 3 string parameters on condition the > 1st and 3rd are NOT empty or NULL. > xCat (s1, s2, s3) > s2 would be a connector that appears only if s1 and s3 are set. > > NULL and an empty string '' should be handled the same. > > e.g. > 'PostgreSQL', ' is ', ' great'--> 'PostgreSQL is great' > NULL, ' is ', ' great' --> 'great' > 'PostgreSQL', ' is ', NULL--> 'PostgreSQL' > NULL, ' is ', NULL --> NULL > 'PostgreSQL', NULL, ' great'--> 'PostgreSQL great' Something like that? test=*# select a,b,c, length(a), length(b), length(c) from string ; a | b | c | length | length | length ++---+++ PostgreSQL | is | great | 10 | 2 | 5 PostgreSQL | is | | 10 | 2 | PostgreSQL || | 10 || | is | || 2 | | is | | 0 | 2 | | is | | 0 | 2 | 0 (6 rows) test=*# test=*# select case when (a is null and c is null) or (a = '' and c = '') then null else coalesce(a,'') || coalesce(b,'')||coalesce(c,'') end from string; case --- PostgreSQLisgreat PostgreSQLis PostgreSQL is (6 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How concat 3 strings if 2 are not empty?
No. B should only appear if A and C are not empty. B is just a filler. Thanks Andreas A. Kretschmer schrieb: In response to Andreas : I'd like a function that concats 3 string parameters on condition the 1st and 3rd are NOT empty or NULL. xCat (s1, s2, s3) s2 would be a connector that appears only if s1 and s3 are set. NULL and an empty string '' should be handled the same. e.g. 'PostgreSQL', ' is ', ' great'--> 'PostgreSQL is great' NULL, ' is ', ' great' --> 'great' 'PostgreSQL', ' is ', NULL--> 'PostgreSQL' NULL, ' is ', NULL --> NULL 'PostgreSQL', NULL, ' great'--> 'PostgreSQL great' Something like that? test=*# select a,b,c, length(a), length(b), length(c) from string ; a | b | c | length | length | length ++---+++ PostgreSQL | is | great | 10 | 2 | 5 PostgreSQL | is | | 10 | 2 | PostgreSQL || | 10 || | is | || 2 | | is | | 0 | 2 | | is | | 0 | 2 | 0 (6 rows) test=*# test=*# select case when (a is null and c is null) or (a = '' and c = '') then null else coalesce(a,'') || coalesce(b,'')||coalesce(c,'') end from string; case --- PostgreSQLisgreat PostgreSQLis PostgreSQL is (6 rows) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Funtion to clean up strings?
Andreas writes: > Hi > Thanks, that really works :) > > Now a last extension. > Some numbers were entered in a "110% perfect" way with an excessive (0). +49 > (0) 123 / 456 789 > I have to suspect the source liked to express that it's either +49 or 0 if > the +49 isn't applicable, but not both. This is the standard format for phone numbers. Parenthesized digits -- as you suspected -- represent digits which must only be dialled when using the number locally and must be omitted from outside. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
