Teradata String Functions helps to perform case transformation', get Length & Positions of required characters and String Conversion

Teradata String Functions Part 2

• LOWER
• UPPER
• INITCAP
• LENGTH
• CHARACTER_LENGTH
• CHAR_LENGTH
• CHAR
• INDEX
• POSITION
• INSTR
• SUBSTR
• SUBSTRING
• LEFT
• RIGHT
• REVERSE
• TRIM
• LTRIM
• RTRIM
• OREPLACE
• OTRANSLATE
• LPAD
• RPAD
• ASCII
• CHR
• TRANSLATE
• TRANSLATE_CHK
• Occurence of Character

• 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
  

• 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).

• 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