Teradata String Functions helps to perform variety of String related operation, click on the required function to get detail
- SUBSTR / SUBSTRING : Extracts the specified characters from the string.
Syntax: SUBSTR(source_string,start_position[,lenght_of_string_To_be_extracted])
SELECT SUBSTR('TERADATA',3,4);
Output: RADA
Syntax: SUBSTRING(source_string FROM start_position [FOR lenght_of_string_To_be_extracted])
SELECT SUBSTRING('TERADATA' from 5 for 4);
Output: DATA
Note: SUBSTR and SUBSTRING behave identically except when the session client character set is KanjiEBCDIC, the server character set is KANJI1, and the parent string contains a multibyte character.
- LEFT : Returns specified number of characters from left side of the source string.
Syntax: LEFT(source_string, number_of_characters_required)
SELECT LEFT('PRADEEP',3);
Output: PRA
- RIGHT : Returns specified number of characters from right side of the source string.
Syntax: RIGHT(source_string, number_of_characters_required)
SELECT RIGHT('PRADEEP',3);
Output: EEP
- REVERSE : Reverses the input string (Introduced in TD 15).
Syntax: REVERSE(source_string)
SELECT REVERSE('Pradeep');
Output: peedarP
- TRIM: Trim white spaces or specific characters from start/end or from both side of the string.
SELECT TRIM(' TERADATA '); --Trims from both sides
Output: TERADATA
SELECT TRIM(TRAILING FROM ' TERADATA '); --Trims from right sides
Output: ' TERADATA'
SELECT TRIM(LEADING FROM ' TERADATA '); --Trims from left sides
Output: 'TERADATA '
SELECT TRIM(LEADING '0' FROM '00033'); --Trims starting '0' from string
Output: '33'
- LTRIM: Trim white spaces or specific characters from Left side the string.
SELECT LTRIM(' TERADATA '); --Trims from left sides
Output: 'TERADATA '
SELECT LTRIM('00033','0'); --Trims starting '0' from string
Output: '33'
- RTRIM: Trim white spaces or specific characters from right side the string.
SELECT RTRIM(' TERADATA '); --Trims from right sides
Output: ' TERADATA'
SELECT RTRIM('00033','3'); --Trims ending '3' from string
Output: '000'
- OREPLACE: Replaces every occurrence of search string with the replace string,if 'replace_string' is not specified then all occurence will be removed from source string.
Syntax: OREPLACE(source_string,search_string[,replace_string])
SELECT OREPLACE('Teradata support high volume of data','data','byte');
Output: Terabyte support high volume of byte
- OTRANSLATE: Replaces every occurrence of search string with the replace string,if 'replace_string' is not specified then all occurence will be removed from source string.
Syntax: OTRANSLATE(source_string,search_characters[,replace_characters])
SELECT OTRANSLATE('Speak Less','pka','lpe'); -- Characters 'p' & 'k' are replaced with 'l' and 'p' respectively
Output: Sleep Less
SELECT OTRANSLATE('Slice','Sl','D'); -- Characters 'S' is replaced with 'D'
-- And extra character 'l' in from_string is removed from source string
Output: Dice
- LPAD: Returns the string to the left with characters/spaces so that result string is of given length.
Syntax: LPAD(source_string,length[,fillstring])
SELECT LPAD('Teradata',10);
Output: ' Teradata'
SELECT LPAD('Teradata',11,'I<3');
Output: 'I<3Teradata'
SELECT LPAD('Teradata',5,'I<3');
Output: 'Terad'
- RPAD: Returns the string to the right with characters/spaces so that result string is of given length.
Syntax: RPAD(source_string,length[,fillstring])
SELECT RPAD('Teradata',10);
Output: 'Teradata '
SELECT RPAD('Teradata',11,'<3u');
Output: 'Teradata<3u'
SELECT RPAD('Teradata',5,'<3u');
Output: 'Terad'