SQL Character Fuctions | Lower, Upper, Initcap, Cocat, Substr, Length, Instr, Lpad, Rpad, Trim, Replace Functions

Character functions:

Single-row character functions accept character data as input and can return both character and number values. Character functions can be divided into the following:

Case conversion functions

Lower
     -The Lower function converts the character values into lowercase letters.

Upper
     -The Upper function converts the character values into uppercase letters.

Initcap
     -The Initcap function coverts the first character of each word into uppercase and the remaining characters into lowercase.
Examples:

Character manipulation functions


Concat
  • CONCAT returns char1 concatenated with char2. 
  • Both char1 and char2 can be any of the datatypes CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 
  • The string returned is in the same character set as char1. 

 Substr
  • The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long.
  • SUBSTR calculates lengths using characters as defined by the input character set. 

 Length
  • The LENGTH functions return the length of char. 
  • LENGTHcalculates length using characters as defined by the input character set.

 Instr
  • The INSTR functions search string for substring. 
  • The function returns an integer indicating the position of the character in string that is the first character of this occurrence. 
  • INSTR calculates strings using characters as defined by the input character set.

 Lpad,Rpad
  • LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2. 
  • This function is useful for formatting the output of a query.

 Trim
  • TRIM enables you to trim leading or trailing characters (or both) from a character string
  • LTRIM - LTRIM removes from the left end of char all of the characters contained in set. 
  • If you do not specify set, it defaults to a single blank.
  • RTRIM - RTRIM removes from the right end of char all of the characters that appear in set. 
  • This function is useful for formatting the output of a query.

 Replace
  • REPLACE function replaces characters from the input string with a given character.