Teradata String Function



Teradata supports several string functions to manipulate the string.

UPPER & LOWER Function

The UPPER and LOWER functions covert 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 string “teradatapoint” into upper case.

SELECT UPPER('teradatapoint');

 *** Query completed. One row found. One column returned.
  *** Total elapsed time was 1 second.

Upper('teradatapoint')                               
 ----------------------
 TERADATAPOINT

The following example will convert the string “TERADATAPOINT” to lower case.

SELECT LOWER('TERADATAPOINT');

 *** Query completed. One row found. One column returned.
  *** Total elapsed time was 1 second.

Lower('TERADATAPOINT')
 ----------------------
 teradatapoint

 

CHARACTER_LENGTH Function

The CHARACTER_LENGTH function returns the numbers of characters of character string expression.

  • The result will be a integer number that represent the length.
  • The result will be same for fixed length character.
  • Result will vary for variable length character.
  • Spaces are valid character so length will be counted for space.

Syntax

CHARACTER_LENGTH ( expression )

Example

The following example will return the number of characters of the string “TERADATAPOINT”.

SELECT CHARACTER_LENGTH('TERADATAPOINT');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

Characters('TERADATAPOINT')
---------------------------
                         13

 

TRIM Function

The TRIM function is used to remove space a particular set of leading or trailing or both from a expression. By default it removes space from both. TRIM is ANSI standard.

Syntax

TRIM ( [ LEADING | BOTH | TRAILING ] [ trim_character] FROM expression)

Example

The following example remove the space from the both end of the string.

SELECT TRIM('TERADATAPOINT');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

Trim(BOTH FROM 'TERADATAPOINT')
-------------------------------
TERADATAPOINT

 

POSITION Function

The POSITION function is used to return the position of one string inside another. Only the position of first occurrence of the string is returned. 

Syntax

POSITION ( expression1 IN expression2 )

Example

The following example will return the first occurrence of point in the string “teradatapoint”.

SELECT POSITION('POINT' IN 'TERADATAPOINT');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

Position('POINT' in 'TERADATAPOINT')
------------------------------------
                                   9

 

SUBSTRING Function

The SUBSTRING is used to positionally extract text from another data value. SUBSTRING is ANSI standard.

Syntax

SUBSTRING ( expression1 FROM n1 [ for n2 ] )

It returns a substring of expression1, starting at the position n1, for a length of n2(if present) or to the end of the string(if not present).

Example

The following example returns the character from 5th position for 4 characters.

SELECT SUBSTRING('TERADATAPOINT' FROM 5 FOR 4);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

Substring('TERADATAPOINT' From 5 For 4)
---------------------------------------
DATA