Teradata concatenate – syntax and example

Teradata concatenate operator links 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: Teradata 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:

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:

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:

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:

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:

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:

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:

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: