Concatenation operator and Concat function in Teradata with examples

Teradata allows us to concatenate the string or numeric values using either concatenation operator or concat() function.

  • Concatenation operator (||)
  • Concat( ) function

Concatenation operator (||)

The concatenation operator(||) is used to join/merge/combine the different strings in Teradata. If we want to concatenate the column values in the select statement,we can just specify the column name ,concatenation operator and the another column name in the select query.

Example : 1

Here we are joining two different strings(Revisit,Class) into one string using concatenation operator (||) as below.

Example for Concatenation operator (||) in Teradata
Example for Concatenation operator (||) in Teradata

Example : 2

Employee table in Teradata
Employee table in Teradata

Lets concatenate the First Name,Middle Initial and Last Name from the Employee table using the concatenation operator(||).

Output

Concatenate columns in Teradata
Concatenate columns in Teradata

Concat() function in Teradata

Concat is a string function that used to join or concatenate the string or numeric values in Teradata. If we pass the arguments such as String values or column names to Concat function,It will return the concatenated strings.

It allows to pass the ‘N’ number of inputs in the function argument.If we specify the number as one of the input in the Concat function, it will concatenate those numbers with spaces.

Example 1: Concat string values

The input values (Computer,Science) are passed as arguments in the concat function. The given values are concatenated using concat function and It returned the output as below.

Example for Concat function in Teradata
Example for Concat function in Teradata

Example 2: Concat string columns

The column names are specified as arguments in the concat function. The firstname,middleinitial and last name columns are concatenated and the concat function returned the Name as below.

Concatenate the column values using Concat function in Teradata
Concatenate the column values using Concat function in Teradata

Example 3: Concatenate numbers with strings

The extra space is added before the number value in the result of Concat function as below.

Concatenate numbers with string in Teradata
Concatenate numbers with string in Teradata

Example 4 : Concatenate numbers

Since the given values are in numeric format, the concat function added the extra space before the values in the result set.

Concatenate numbers in Teradata
Concatenate numbers in Teradata

Example 5: Concatenate numbers that represent in String format

Since the numeric values are represented in string format, the concat function is not added the extra space in the result set.

Concatenate numbers as strings in Teradata
Concatenate numbers as strings in Teradata