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.

String function	Explanation
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.

Syntax

UPPER (expression) -> returns expression as uppercase 
LOWER (expression) -> returns expression as lowercase 

Example

The following example will convert the "Robert" string in the upper case string.

        /*create a table called NAMES */
CREATE TABLE NAMES(Name text);
       /*create few records in this table */
SELECT UPPER('Robert');
       /*Query completed. One row found. One column returned */
       /*Total elapsed time was 0.00 second */
       /*Display all the records from the table */
SELECT * FROM NAMES;

After executing the above code, it will give the following output.

ROBERT

Now in the same example, we will convert the same "ROBERT" string in the lower case string.

SELECT LOWER('ROBERT');
      /*Query completed. One row found. One column returned */
     /*Total elapsed time was 0.00 second */

Output

robert

CHARACTER_LENGTH Function

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.

Syntax

Example

The following example will return the number of characters of the "Robert" string.

SELECT CHARACTER_LENGTH('Robert');
          /* Query completed. One row found. One column returned */
         /* Total elapsed time was 0.00 second */

Execute the above code, and it tells the length of the "Robert" string as the output shown below.

TRIM Function

The TRIM function is used to remove space a particular set of leading or trailing or both from an expression. TRIM is ANSI standards.

Syntax

Example

The following example removes the space from both the end of the "Robert" string.

SELECT TRIM('   Robert    ');
            /* Query completed. One row found. One column returned */
            /* Total elapsed time was 0.00 second */

When we execute the above code, it will trim the existing space from both ends of the string and gives the following output.

Robert

POSITION Function

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.

Syntax

Example

The following example will return the occurrence of "e" in the "Robert" string.

SELECT POSITION("e" IN "Robert");
          /* Query completed. One row found. One column returned */
         /* Total elapsed time was 0.00 second */

After executing the above code, it will find the position of "e" substring in the "Robert" string as output.

SUBSTRING Function

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.

Syntax

It returns a string (str), starting at the position (pos), and length (len) in characters.

Example

The following example returns the character from the 1^st position for 3 characters.

SELECT SUBSTRING('Robert' FROM 1 FOR 3);
         /* Query completed. One row found. One column returned */
        /* Total elapsed time was 0.00 second */

The above code returns 3 characters from the 1st position of the string "Robert" as the output.