Teradata concatenate – syntax and example

teradata

In Teradata, we can use concatenation operator to link multiple string or numeric or expression values into single value. Input for concatenate can be two or more but output of concat is always one. In Teradata, concatenate operator is represented by “||” i.e two vertical lines. You can also use ‘!!’ i.e. two exclamation mark however it is not recommended to use it. In this post we will see syntax and examples of various scenarios where concatenate can be used.

Let’s create a dummy table and load it with some sample records. We will use this table in subsequent examples to highlight usage of concatenate in Teradata.

CREATE volatile TABLE emp (
  emp_no integer ,
  birth_date date ,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) ,
  gender char(1)
)
UNIQUE PRIMARY INDEX (emp_no)
ON COMMIT PRESERVE ROWS;


INSERT INTO emp VALUES(10001,'1978-09-02','Georgi','Facello','M');
INSERT INTO emp VALUES(10002,'1989-06-02','Bezalel','Simmel','F');
INSERT INTO emp VALUES(10003,'1984-12-03','Parto','Bamford','M');
INSERT INTO emp VALUES(10004,'1979-05-01','Chirstian',NULL,'M');
INSERT INTO emp VALUES(10005,'1980-01-21','Kyoichi','Maliniak','M');
INSERT INTO emp VALUES(10006,'1978-04-20','Anneke','Preusig','F');
INSERT INTO emp VALUES(10007,NULL,'Tzvetan','Zielinski','F');
INSERT INTO emp VALUES(10008,'1983-02-19','Saniya',NULL,'M');
INSERT INTO emp VALUES(10009,'1977-04-19','Sumant','Peac','F');
INSERT INTO emp VALUES(10010,'1988-06-01','Duangkaew','Piveteau',NULL);

Case 1: Concatenate 2 columns

We can concatenate 2 columns by specifying the column names and adding the concatenate operator (||) in between.

select
	emp_no,
	first_name,
	last_name,
	first_name || last_name as full_name
from
	emp
where
	emp_no<10004;

In the above example we have concatenated first_name & last_name to create a new derived column which is full_name.

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloGeorgiFacello
10,002BezalelSimmelBezalelSimmel
10,003PartoBamfordPartoBamford

Now in the output we can see concatenate output without spaces which may not be desired output in most of the cases.

Case 2: Concatenate 2 columns with space

If you want to concatenate multiple columns and have space in between in output then add a space using extra concatenate operator in the query.

select
	emp_no,
	first_name,
	last_name,
	first_name || ' ' || last_name as full_name
from
	emp
where
	emp_no<10004;

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloGeorgi Facello
10,002BezalelSimmelBezalel Simmel
10,003PartoBamfordParto Bamford

Case 3: Concatenate 2 columns with delimiter

In the above output you can see we have used space (‘ ‘) as separator or delimiter. Similarly you can use any other character as delimiter too.

select
	emp_no,
	first_name,
	last_name,
	first_name || '-' || last_name as full_name
from
	emp
where
	emp_no<10004;

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloGeorgi-Facello
10,002BezalelSimmelBezalel-Simmel
10,003PartoBamfordParto-Bamford

In the above output you can see we have used “-” as separator in the concatenate output. You can use any other character or even string as delimiter.

Case 4: Concatenate columns with NULL

So we have seen in earlier examples how to concatenate 2 columns , add a space in between and also how to add a separator. In this example we will see how to concatenate columns with NULL values in it.

select
	emp_no,
	first_name,
	last_name,
	first_name || ' ' || last_name as full_name
from
	emp;

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloGeorgi Facello
10,002BezalelSimmelBezalel Simmel
10,003PartoBamfordParto Bamford
10,004Chirstian[NULL][NULL]
10,005KyoichiMaliniakKyoichi Maliniak
10,006AnnekePreusigAnneke Preusig
10,007TzvetanZielinskiTzvetan Zielinski
10,008Saniya[NULL][NULL]
10,009SumantPeacSumant Peac
10,010DuangkaewPiveteauDuangkaew Piveteau

In the above output, I want you to check output for emp_no 10004 & 10008. We can see output is NULL for full_name column. This is slightly different output than expected. In Teradata, if you concatenate NULL with anything then unlike other RDBMS , output in TERADATA is NULL. So please be very careful while using CONCATENATE in Teradata.

You can fix this with below 2 steps:

  1. Apply concatenate operator on NOT NULL columns
  2. If applied on columns which can have NULL values then apply coalesce function.
select
	emp_no,
	first_name,
	last_name,
	first_name || ' ' || coalesce(last_name,
	'') as full_name
from
	emp;

In the above SQL , we have added coalesce(last_name,”) to handle NULL value and hence avoided output becoming NULL too.

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloGeorgi Facello
10,002BezalelSimmelBezalel Simmel
10,003PartoBamfordParto Bamford
10,004Chirstian[NULL]Chirstian
10,005KyoichiMaliniakKyoichi Maliniak
10,006AnnekePreusigAnneke Preusig
10,007TzvetanZielinskiTzvetan Zielinski
10,008Saniya[NULL]Saniya
10,009SumantPeacSumant Peac
10,010DuangkaewPiveteauDuangkaew Piveteau

Case 5: Concatenate columns with different data type

You can also concatenate NUMERIC columns like integer with STRING columns like varchar. In this example , we will concatenate emp_no(integer) to first_name & last_name (varchar)

select
	emp_no,
	first_name,
	last_name,
	emp_no || '-' || first_name || ' ' || coalesce(last_name,
	'') as full_name
from
	emp;

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacello 10001-Georgi Facello
10,002BezalelSimmel 10002-Bezalel Simmel
10,003PartoBamford 10003-Parto Bamford
10,004Chirstian[NULL] 10004-Chirstian
10,005KyoichiMaliniak 10005-Kyoichi Maliniak
10,006AnnekePreusig 10006-Anneke Preusig
10,007TzvetanZielinski 10007-Tzvetan Zielinski
10,008Saniya[NULL] 10008-Saniya
10,009SumantPeac 10009-Sumant Peac
10,010DuangkaewPiveteau 10010-Duangkaew Piveteau

Case 6: Concatenate columns with expression

You can concatenate expression with column values too using concatenate operator in Teradata. Let’s look at the example below in which we have created a derived variable using CASE expression and also we have extracted sub-string of last_name column in concatenation.

select
	emp_no,
	first_name,
	last_name,
	case when gender = 'F' then 'Ms.' else 'Mr.' end || ' ' 
	|| first_name 
	|| ' ' 
	|| coalesce(
		substr(last_name,1,1),
		'') as full_name
	from
		emp;

Output:

emp_nofirst_namelast_namefull_name
10,001GeorgiFacelloMr. Georgi F
10,002BezalelSimmelMs. Bezalel S
10,003PartoBamfordMr. Parto B
10,004Chirstian[NULL]Mr. Chirstian
10,005KyoichiMaliniakMr. Kyoichi M
10,006AnnekePreusigMs. Anneke P
10,007TzvetanZielinskiMs. Tzvetan Z
10,008Saniya[NULL]Mr. Saniya
10,009SumantPeacMs. Sumant P
10,010DuangkaewPiveteauMr. Duangkaew P

In some case, while combining expressions or columns you may see extra spaces in the output. In such cases, feel free to add TRIM function to remove unnecessary spaces if any.

If you prefer to watch videos then check the video below:

Leave a Reply

Your email address will not be published. Required fields are marked *