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