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:
emp_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | GeorgiFacello |
10,002 | Bezalel | Simmel | BezalelSimmel |
10,003 | Parto | Bamford | PartoBamford |
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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | Georgi Facello |
10,002 | Bezalel | Simmel | Bezalel Simmel |
10,003 | Parto | Bamford | Parto 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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | Georgi-Facello |
10,002 | Bezalel | Simmel | Bezalel-Simmel |
10,003 | Parto | Bamford | Parto-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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | Georgi Facello |
10,002 | Bezalel | Simmel | Bezalel Simmel |
10,003 | Parto | Bamford | Parto Bamford |
10,004 | Chirstian | [NULL] | [NULL] |
10,005 | Kyoichi | Maliniak | Kyoichi Maliniak |
10,006 | Anneke | Preusig | Anneke Preusig |
10,007 | Tzvetan | Zielinski | Tzvetan Zielinski |
10,008 | Saniya | [NULL] | [NULL] |
10,009 | Sumant | Peac | Sumant Peac |
10,010 | Duangkaew | Piveteau | Duangkaew 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:
- Apply concatenate operator on NOT NULL columns
- 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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | Georgi Facello |
10,002 | Bezalel | Simmel | Bezalel Simmel |
10,003 | Parto | Bamford | Parto Bamford |
10,004 | Chirstian | [NULL] | Chirstian |
10,005 | Kyoichi | Maliniak | Kyoichi Maliniak |
10,006 | Anneke | Preusig | Anneke Preusig |
10,007 | Tzvetan | Zielinski | Tzvetan Zielinski |
10,008 | Saniya | [NULL] | Saniya |
10,009 | Sumant | Peac | Sumant Peac |
10,010 | Duangkaew | Piveteau | Duangkaew 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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | 10001-Georgi Facello |
10,002 | Bezalel | Simmel | 10002-Bezalel Simmel |
10,003 | Parto | Bamford | 10003-Parto Bamford |
10,004 | Chirstian | [NULL] | 10004-Chirstian |
10,005 | Kyoichi | Maliniak | 10005-Kyoichi Maliniak |
10,006 | Anneke | Preusig | 10006-Anneke Preusig |
10,007 | Tzvetan | Zielinski | 10007-Tzvetan Zielinski |
10,008 | Saniya | [NULL] | 10008-Saniya |
10,009 | Sumant | Peac | 10009-Sumant Peac |
10,010 | Duangkaew | Piveteau | 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_no | first_name | last_name | full_name |
---|---|---|---|
10,001 | Georgi | Facello | Mr. Georgi F |
10,002 | Bezalel | Simmel | Ms. Bezalel S |
10,003 | Parto | Bamford | Mr. Parto B |
10,004 | Chirstian | [NULL] | Mr. Chirstian |
10,005 | Kyoichi | Maliniak | Mr. Kyoichi M |
10,006 | Anneke | Preusig | Ms. Anneke P |
10,007 | Tzvetan | Zielinski | Ms. Tzvetan Z |
10,008 | Saniya | [NULL] | Mr. Saniya |
10,009 | Sumant | Peac | Ms. Sumant P |
10,010 | Duangkaew | Piveteau | Mr. 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: