Teradata String Functions are primarily used for various string manipulation. It also supports most of the standard string function along with the Teradata extension to those functions.
Teradata String Functions
Below are the commonly used Teradata string functions:
Read:
- Teradata Regular Expressions and Examples
- Teradata Set Operators: UNION, UNION ALL, INTERSECT, EXCEPT/MINUS
- Commonly used Teradata Analytics Functions and Examples
- Teradata Date Functions and Examples
Function | Description |
concat(string1, …, stringN) | Returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||). |
length(string) | Returns number of characters in the string |
lower(string) | Converts string to lower case |
upper(string) | Converts string to upper case |
lpad(string, size, padstring) | Pads the left side of string with characters to create a new string. |
rpad(string, size, padstring) | Pads the right side of string with characters to create a new string. |
ltrim(string) | Removes leading whitespaces from string |
rtrim(string) | Removes training whitespaces from string |
replace(string, search) | Remove search string from the given string |
replace(string, search, replace) | Replace all instances of search with replace string |
reverse(string) | Return string characters in reverse order |
split(string, delimiter) | Split given string on delimiter. This function returns array of string |
strpos(string, substring) | Return staring position first instance of substring in given string |
position(substring IN string) | Return staring position first instance of substring in given string |
substr(string, start, length) | Returns a substring of string that begins at positionstart and is length characters long |
trim(string) | Removes leading and trailing whitespace from given string |
chr(n) | Returns the character with the specified ASCII value. |
to_utf8(string) | Encodes string into a UTF-8 varbinary representation. |
from_utf8(binary) | Decodes a UTF-8 encoded string from binary |
select otranslate(string, from, to); | Replaces any character in string that matches a character in the from set with the corresponding character in the to set |
Teradata String Functions Examples
Below are some of sample example on Teradata string functions.
Teradata pad zero using String functions:
select lpad('1234',8,'0'); lpad('1234',8,'0') 00001234 select rpad('1234',8,'0'); rpad('1234',8,'0') 12340000
Teradata Sub-string function Examples:
select substr('1234789464361',1,5); Substr('1234789464361',1,5) 12347
The Teradata Translate function Examples:
select otranslate('123CDR123','CDR','123'); otranslate('123CDR123','CDR','123') 123123123
Teradata trim function Examples:
select trim (' 1234567891 '); Trim(BOTH FROM ' 1234567891 ') 1234567891
Excellent and neat explanation
Thank you Siva 🙂