Teradata String Manipulation
Teradata provides several functions to manipulate the strings. These functions are compatible with the ANSI standard.
Teradata String Functions are also supported most of the standard string functions along with the Teradata extension to those functions.
|Concat (string1, …, stringN)||It returns the concatenation of two or more string values. This function provides the same functionality as the SQL-standard concatenation operator (||).|
|Length (string)||It returns the number of characters in the string.|
|Lower (string)||It converts a string to lower case.|
|Upper (string)||It converts a string to upper case.|
|Lpad (string, size, padstring)||Pads the left side of the string with characters to create a new string.|
|Rpad (string, size, padstring)||Pads the right side of the string with characters to create a new string.|
|Trim (string)||It removes leading and trailing whitespace from the given string.|
|Ltrim (string)||It removes leading whitespaces from the string.|
|Rtrim (string)||It removes trailing whitespaces from the string.|
|Replace (string, search)||It removes the search string from the given string.|
|Replace (string, search, replace)||It replaces all instances of search with replacing string.|
|Reverse (string)||It returns string characters in reverse order.|
|Split (string, delimiter)||Split given string on the delimiter. This function returns an array of string.|
|Strops (string, substring)||It returns the staring position first instance of a substring in a given string.|
|Position (substring IN string)||It returns the staring position first instance of a substring in a given string.|
|Substr (string, start, length)||It returns a substring of string that begins at the start position and is length characters long.|
|Chr (n)||It returns the character with the specified ASCII value.|
|to_utf8 (string)||It encodes a string into a UTF-8 varbinary representation.|
|from_utf8 (binary)||It decodes a UTF-8 encoded string from binary.|
|Select translate (string, from, to);||It can replace any character in the string that matches a character in the form set with the corresponding character in the set.|
|Index (string)||It locates the position of a character in a string (Teradata extension).|
UPPER & LOWER Function
The UPPER and LOWER functions convert the character column values all in uppercase and lowercase, respectively. UPPER and LOWER are ANSI compliant.
The following example will convert the “Robert” string in the upper case string.
After executing the above code, it will give the following output.
Now in the same example, we will convert the same “ROBERT” string in the lower case string.
The CHARACTER_LENGTH function returns the numbers of characters of a character string expression.
- The result will be an integer number that represents the length.
- The result will be the same for the fixed-length character.
- The result will vary for the variable-length character.
- Spaces are valid characters so that the length will be counted for space.
The following example will return the number of characters of the “Robert” string.
Execute the above code, and it tells the length of the “Robert” string as the output shown below.
The TRIM function is used to remove space a particular set of leading or trailing or both from an expression. TRIM is ANSI standards.
The following example removes the space from both the end of the “Robert” string.
When we execute the above code, it will trim the existing space from both ends of the string and gives the following output.
The POSITION function is used to return the position of a substring inside the string. The position of the first occurrence of the string is returned only.
The following example will return the occurrence of “e” in the “Robert” string.
After executing the above code, it will find the position of “e” substring in the “Robert” string as output.
The SUBSTRING function is used to return a specified number of characters from a particular position of a given string. SUBSTRING function is ANSI standard.
It returns a string (str), starting at the position (pos), and length (len) in characters.
The following example returns the character from the 1st position for 3 characters.
The above code returns 3 characters from the 1st position of the string “Robert” as the output.