SQL Character Functions

CHR returns the character having the binary equivalent to n as a VARCHAR2 value.
This function takes as an argument a NUMBER value,or any value that can be implicitly converted to NUMBER,and returns a character.
EX:SELECT CHR(67)CHR(65)CHR(84) FROM DUAL;Output is CAT

CONCAT returns char1 concatenated with char2.This function is equivalent to the concatenation operator (). The function is useful when there are spaces in the values to be concatenated. The concatenation operator does not permit spaces.

INITCAP returns char,with the first letter of each word in uppercase,all other letters in lowercase.Words are delimited by white space or characters that are not alphanumeric.
EX:SELECT INITCAP('the boy') FROM DUAL;Output is The Boy.

LOWER returns char, with all letters lowercase.
EX:SELECT LOWER('HOUSE') from dual;Output is house.

UPPER returns char, with all letters uppercase.
EX:SELECT LOWER('house') from dual;Output is HOUSE.

LPAD(exp1,n,exp2) 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.If expr2 is not specified,then the default is a single blank.If expr1 is longer than n,then this function returns the portion of expr1 that fits in n.The argument n is the total length of the return value as it is displayed on your terminal screen.
EX:SELECT LPAD('Home',10,'*') from dual;Output is ******Home.

RPAD(exp1,n,exp2) returns expr1,right-padded to n characters with expr2.If you do not specify expr2, then it defaults to a single blank. If expr1 is longer than n, then this function returns the portion of expr1 that fits in n.
The argument n is the total length of the return value as it is displayed on your screen.
EX:SELECT RPAD('Home',10,'*') from dual;Output is Home******.

LTRIM removes all characters contained in specified set in its .If you do not specify set,it defaults to a single blank.If char is a character literal,then you must enclose it in single quotes.Oracle Database begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.
EX:SELECT LTRIM('***WORD','*') FROM DUAL;Output is WORD.

RTRIM removes all of the characters that appear in set from the right end.If you do not specify set,it defaults to a single blank.If char is a character literal,then you must enclose it in single quotes.
EX:SELECT RTRIM('WORD***','*') FROM DUAL;Output is WORD.

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character is a character literal,then it should be enclosed in single quotes.
EX:SELECT TRIM('**WORD***','*') FROM DUAL;Output is WORD.

REPLACE returns char with every occurrence of search_string replaced with replacement_string.If replacement_string is omitted or null,then all occurrences of search_string are removed.If search_string is null,then char is returned.
EX:SELECT REPLACE('RED','R','B') FROM DUAL;Output is BED.

SUBSTR functions return a portion of string,beginning at character position, substring_length characters long.If position is 0,then it is treated as 1.If position is positive,it counts from the beginning of string to find the first character.If position is negative,it counts backward from the end of string.If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then Oracle returns null.
EX:SELECT SUBSTR('MYHOUSE',3,4) "Substring" FROM DUAL;Output is HOUS

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. If expr is a character string, then you must enclose it in single quotation marks.TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.
EX:SELECT ename from emp WHERE SOUNDEX(ename)= SOUNDEX('SMYTHE');