Teradata String Functions and Examples

  • Post author:
  • Post last modified:February 26, 2018
  • Post category:General
  • Reading time:4 mins read

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

Teradata String Functions

Below are the commonly used Teradata string functions:

Read:

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

This Post Has 2 Comments

  1. Siva

    Excellent and neat explanation

    1. Vithal S

      Thank you Siva 🙂

Comments are closed.