String fields concat

Database
Highlighted
Enthusiast

String fields concat

Hi All,

 

Quick help please

I have a source table as below

 

COL1COL2COL3COL4
CPHYVFDENIND
NZSL USA
CLTJFK  
 NLRNDRUK

        

I would like to concat all the 4 columns with a delimiter comma(,). Consider only the not null column values and concat.

if the column value is null/empty , then ignore it.

 

expeced output:

=============

CPH,YVF,DEN,IND
NZ,SL,USA
CLT,JFK
NLR,NDR,UK

 

Thanks in advacne for your help:)

 

 


Accepted Solutions
Enthusiast

Re: String fields concat

Try this as well:

 

SEL CASE
         WHEN SUBSTR(OUTPUT,1,1)=','
         THEN   SUBSTR(OUTPUT,2) 
         ELSE OUTPUT
         END FROM (SEL 
  CASE WHEN  COALESCE(COL1,'') <>''
                          THEN (COL1)
                          ELSE COALESCE(COL1,'')
                          END
||  CASE WHEN  COALESCE(COL2,'') <>''
                          THEN (','||COL2)
                          ELSE COALESCE(COL2,'')
                          END
||  CASE WHEN  COALESCE(COL3,'') <>''
                          THEN (','||COL3)
                          ELSE COALESCE(COL3,'')
                          END
||CASE WHEN  COALESCE(COL4,'') <>''
                          THEN (','||COL4)
                          ELSE COALESCE(COL4,'')
                          END
                  AS OUTPUT
FROM SCHEMA.TABLE_NAME) A
1 ACCEPTED SOLUTION
4 REPLIES 4
Ambassador

Re: String fields concat

Hi,

 

You can do it with the following:

COALESCE(COL1,'')||COALESCE(COL2,'')||COALESCE(COL3,'')||COALESCE(COL4,'')

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: String fields concat

Hi anitgo,

 

To handle support for nulls, empty and proper commas I'll expand Dave's suggestion to this :

create multiset volatile table mvt_datas, no log
( mvt_pi    byteint
, col1      varchar(3)
, col2      varchar(3)
, col3      varchar(3)
, col4      varchar(3)
)
primary index (mvt_pi)
on commit preserve rows;

insert into mvt_datas values (1, 'CPH', 'YVF', 'DEN', 'IND');
insert into mvt_datas values (1, 'NZ' , 'SL' , null , 'USA');
insert into mvt_datas values (1, 'CLT', 'JFK', null , ''   );
insert into mvt_datas values (1, ''   , 'NLR', 'NDR', 'UK' );

select trim(trailing ',' from
       coalesce(nullif(col1, '') || ',', '')
    || coalesce(nullif(col2, '') || ',', '')
    || coalesce(nullif(col3, '') || ',', '')
    || coalesce(nullif(col4, '')       , '')) as val
  from mvt_datas
 where mvt_pi = 1;

val
---------------
CPH,YVF,DEN,IND
NZ,SL,USA
CLT,JFK
NLR,NDR,UK

Ambassador

Re: String fields concat

@WaldarThanks, I'd missed out the comma's.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: String fields concat

Try this as well:

 

SEL CASE
         WHEN SUBSTR(OUTPUT,1,1)=','
         THEN   SUBSTR(OUTPUT,2) 
         ELSE OUTPUT
         END FROM (SEL 
  CASE WHEN  COALESCE(COL1,'') <>''
                          THEN (COL1)
                          ELSE COALESCE(COL1,'')
                          END
||  CASE WHEN  COALESCE(COL2,'') <>''
                          THEN (','||COL2)
                          ELSE COALESCE(COL2,'')
                          END
||  CASE WHEN  COALESCE(COL3,'') <>''
                          THEN (','||COL3)
                          ELSE COALESCE(COL3,'')
                          END
||CASE WHEN  COALESCE(COL4,'') <>''
                          THEN (','||COL4)
                          ELSE COALESCE(COL4,'')
                          END
                  AS OUTPUT
FROM SCHEMA.TABLE_NAME) A