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
|
1 |
SELECT 'REVISIT' || 'CLASS' |
Here we are joining two different strings(Revisit,Class) into one string using concatenation operator (||) as below.
Example : 2
Lets concatenate the First Name,Middle Initial and Last Name from the Employee table using the concatenation operator(||).
|
1 2 3 4 |
Select Empid,FirstName || MiddleInitial || LastName as Name, Designation from Test_DB.Employee; |
Output
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
|
1 |
Select concat('Computer','Science'); |
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 2: Concat string columns
|
1 2 3 4 5 |
Select Empid, Concat(FirstName,MiddleInitial,LastName) as Name, Designation from Test_DB.Employee; |
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.
Example 3: Concatenate numbers with strings
|
1 |
Select concat('Teradata','Database',16); |
The extra space is added before the number value in the result of Concat function as below.
Example 4 : Concatenate numbers
|
1 |
Select concat(129,543,16); |
Since the given values are in numeric format, the concat function added the extra space before the values in the result set.
Example 5: Concatenate numbers that represent in String format
|
1 |
Select concat('129','543','16'); |
Since the numeric values are represented in string format, the concat function is not added the extra space in the result set.
Recommended Articles
- How to extract the substring from the value of a column in Teradata?
- Position function in Teradata?
- How to split the string based on delimiter in Teradata?
- Replace the string using REGEXP_REPLACE function in Teradata
