Teradata String Functions Part 2
Teradata String Functions helps to perform variety of String manipulation operations, click on the required function to get detail
Case Transformation
- LOWER: Change the case of string to lower case.
SELECT LOWER('TERAData');
Output: teradata
- UPPER: Change the case of string to upper case.
SELECT UPPER('TERAData');
Output: TERADATA
- INITCAP: Captialize initial character of each word of the string.
SELECT INITCAP('tERAData learning');
Output: Teradata Learning
String Length & Positions
- LENGTH: Returns number of characters in a string.
SELECT LENGTH('TERAData');
Output: 8
- CHARACTER_LENGTH: Returns number of characters in a string.
SELECT CHARACTER_LENGTH('TERAData');
Output: 8
- CHAR_LENGTH: Returns number of characters in a string.
SELECT CHAR_LENGTH('TERAData');
Output: 8
- CHAR: Returns number of characters in a string.
SELECT CHAR('TERAData');
Output: 8
- INDEX: Returns position of character/characters in a string.
Syntax: INDEX(source_string,search_string)
SELECT INDEX('TERADATA','D');
Output: 5
- POSITION: Returns position of character/characters in a string.
Syntax: POSITION(search_string IN source_string)
SELECT POSITION('D' IN 'TERADATA');
Output: 5
Note: INDEX and POSITION 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.
This is the only case for which the results of these two functions differ when performed on the same data. (Reference: www.info.teradata.com)
- INDEX counts bytes; POSITION counts logical characters.
- INDEX counts Shift-Out and Shift-In characters; POSITION does not.
- INSTR: Searches string for nth occurences of character/characters,below are optional parameters for INSTR.
- Position : Search will begin from this place,if not specified then search will begin from start of string.
- Occurence: Will search that occurence of search_string in source_string,if not specifed then it will search ist occurence.
Syntax: INSTR(source_string,search_string[,position,[occurence]])
SELECT INSTR('TERADATA','A',1,2);
Output: 6
SELECT INSTR('TERADATA','A');
Output: 4
- Find Occurence of character: oReplace and char length can be used to find the number of occurence of a particular character.
select CHAR_LENGTH('teradata') - CHAR_LENGTH(OREPLACE('teradata','a',''));
Output: 3
Explanation
- There are 2 parts of the logic, 1st part is CHAR_LENGTH('teradata') and 2nd part is CHAR_LENGTH(OREPLACE('teradata','a',''))
- First part returns the original length of the String, 8 in the above example.
- In the second part, Replace function has been used to replace character with the blank which will reduce the size of original string by number of occurence of character.
- Char_length is calculated of the modified string after removal of characters.
- In the final step, orginal length of string is subtracted from length of the modified string(after replace of characters).
- In example, length of string(teradata) is 8 and length of modified string(terdt) is 5 as there are 3 'a' characters in teradata. Therefore output is 3 (8-5).
String Conversion
- ASCII: Returns the numerical representation of the character.
SELECT ASCII('A');
Output: 65
- CHR: Returns ASCII value of the the number passed.
SELECT CHR(65);
Output: A
- TRANSLATE: Used to convert string from one character set to another character set.
Syntax: TRANSLATE(source_string USING FRom_CharacterSet_to_CharacterSet)
SELECT TRANSLATE('teradata' USING UNICODE_TO_LATIN);
Output: teradata
- TRANSLATE_CHK: Used to check if conversion from one character set to another character set results in error or not,return greater than 0 if untranslatable.
Syntax: TRANSLATE(source_string USING FRom_CharacterSet_to_CharacterSet)
SELECT TRANSLATE_CHK('我希望利用这个时间制作可' USING UNICODE_TO_LATIN);
Output: 1
Visit String Function part 1 for more string functions