Обсуждение: Is there a conditional string-concatenation ?
Hi, Is there a conditional string-concatenation ? I'd like to have an elegant way to connect 2 strings with some 3rd element between only if there really are 2 strings to connect. e.g. MyCat ( 'John', '_', 'Doe' ) --> 'John_Doe' while MyCat ( 'John', '_', '' ) --> 'John' MyCat ( '', '_', 'Doe' ) --> 'Doe' MyCat ( '', '_', '' ) --> NULL It should treat NULL and '' equally as empty and it should trim each of the 3 elements. so MyCat ( ' John ', '_', NULL ) --> 'John' MyCat ( 'John', NULL, 'Doe' ) --> 'JohnDoe'
On Tue, Oct 12, 2010 at 06:09:07AM +0200, Andreas wrote: > Hi, > Is there a conditional string-concatenation ? Perhaps this: CREATE OR REPLACE FUNCTION mycat(text, text, text) RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$ SELECT CASE WHEN $1 IS NULL OR $1 = '' THEN trim($3) WHEN $3 IS NULL OR $3 = '' THEN trim($1) ELSE trim($1) || trim(coalesce($2,'')) || trim($3) END; -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com
2010/10/12 Andreas <maps.on@gmx.net>: > Hi, > Is there a conditional string-concatenation ? > > I'd like to have an elegant way to connect 2 strings with some 3rd element > between only if there really are 2 strings to connect. > > e.g. > MyCat ( 'John', '_', 'Doe' ) --> 'John_Doe' > while > MyCat ( 'John', '_', '' ) --> 'John' > MyCat ( '', '_', 'Doe' ) --> 'Doe' > MyCat ( '', '_', '' ) --> NULL > > It should treat NULL and '' equally as empty > and it should trim each of the 3 elements. > > so > MyCat ( ' John ', '_', NULL ) --> 'John' > MyCat ( 'John', NULL, 'Doe' ) --> 'JohnDoe' > Try: bdteste=# SELECT nullif(ltrim(rtrim(coalesce(c1,'') || coalesce(c2,'') || coalesce(c3,''),' _'),' _'),'') bdteste-# FROM (VALUES ('John', '_', 'Doe'),('John', '_', ''),('', '_', 'Doe'),('', '_', ''),(' John ', '_', NULL),('John', NULL, 'Doe')) AS foo(c1,c2,c3); nullif ----------John_DoeJohnDoe JohnJohnDoe (6 rows) Osvaldo
Hello more simply postgres=# CREATE OR REPLACE FUNCTION public.foo(a text, b text, c text)RETURNS textLANGUAGE sql AS $function$ SELECT coalesce($1 || $2 || $3, $1 || $2, $2 || $3) $function$ Regards Pavel Stehule 2010/10/12 Osvaldo Kussama <osvaldo.kussama@gmail.com>: > 2010/10/12 Andreas <maps.on@gmx.net>: >> Hi, >> Is there a conditional string-concatenation ? >> >> I'd like to have an elegant way to connect 2 strings with some 3rd element >> between only if there really are 2 strings to connect. >> >> e.g. >> MyCat ( 'John', '_', 'Doe' ) --> 'John_Doe' >> while >> MyCat ( 'John', '_', '' ) --> 'John' >> MyCat ( '', '_', 'Doe' ) --> 'Doe' >> MyCat ( '', '_', '' ) --> NULL >> >> It should treat NULL and '' equally as empty >> and it should trim each of the 3 elements. >> >> so >> MyCat ( ' John ', '_', NULL ) --> 'John' >> MyCat ( 'John', NULL, 'Doe' ) --> 'JohnDoe' >> > > > Try: > bdteste=# SELECT nullif(ltrim(rtrim(coalesce(c1,'') || coalesce(c2,'') > || coalesce(c3,''),' _'),' _'),'') > bdteste-# FROM (VALUES ('John', '_', 'Doe'),('John', '_', ''),('', > '_', 'Doe'),('', '_', ''),(' John ', '_', NULL),('John', > NULL, 'Doe')) AS foo(c1,c2,c3); > nullif > ---------- > John_Doe > John > Doe > > John > JohnDoe > (6 rows) > > Osvaldo > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >