Character Functions returning character Values

CHR

Syntax

CHR(n)
It will return the character that is the binary equivalent of n in the database character set.

Example
SELECT CHR(75) "Character" FROM DUAL
Character
---------
K

CONCAT

Syntax
CONCAT(char1, char2)
It will return char1 concatenated with char2.  This function is equal to the concatenation operator (||).

Example
This example uses nesting to concatenate of three character strings:
SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job" FROM emp
WHERE empno = 7900
Job
-------------------------
JAMES is a CLERK

INITCAP

Syntax

INITCAP(char)
It will return char, with the first letter of every word in uppercase and all other letters in lowercase. Words are delimited through white spaces or characters which are not alphanumeric.

Example
SELECT INITCAP('the soap') "Capitalized" FROM DUAL
Capitalized
--------------------
The Soap

LPAD

Syntax
LPAD(char1,n [,char2])
It will Return char1, left-padded to n length with the sequence of characters in char2; char2 defaults to ' ', a single blank. If the char1 is longer than n, that function returns the portion of char1 which fits in n.
The argument n is the total length of the return value as it is displayed on the screen. In most character sets that is also the number of characters in the return value. Through, in certain multi-byte character sets the display length of a character string can differ from the number of characters in the string.

Example
SELECT LPAD('Page 1',15,'*.') "LPAD example" FROM DUAL
LPAD example
---------------
*.*.*.*.*Page 1

LTRIM

Syntax
LTRIM(char[,Set])

It will Removes the characters which appear in set from the left of char and set defaults to ' ', in a single blank.
Example

SELECT LTRIM('xyxXxyLAST WORD','xy') "Left trim example" FROM DUAL
Left trim example
-----------------
XxyLAST WORD

REPLACE

Syntax
REPLACE(char, search_string[,replacement_string])
Returns char with every occurrence of search_string that is replaced with replacement_string. All occurrences of search_string are removed if replacement_string is omitted or null. If search_string is null then char is returned. This function gives a superset of the functionality provided through the TRANSLATE function. TRANSLATE gives single character, one to one, substitution. REPLACE permits you to substitute one string for another as well as to remove character strings.

Example
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL
Changes
------------
BLACK and BLUE

RPAD

Syntax
RPAD(char1, n [,char2])
It will return char1, right-padded to length n with char2, replicated as several times as necessary. The default padding is  ' ', in a single blank. This function returns the portion of char1 that fits in n if char1 is longer than n.
The argument n is the total length of the return value as it is displayed on the terminal screen. In the most character sets this is also the number of characters in the return value. Through, in different multi-byte character sets the display length of a character string can differ from the number of characters in the string.

Example
SELECT RPAD(ename,11,'ab') "RPAD example" FROM emp WHERE ename = 'TURNER'
RPAD example
------------
TURNERababa

RTRIM

Syntax
RTRIM(char [,set])
It will return char, with all the right-most characters which appear in set removed and set it defaults to ' ', a single blank. RTRIM works as same to LTRIM.

Example
SELECT RTRIM('TURNERyxXxy','xy') "Right trim example" FROM DUAL
Right trim example
------------------
TURNERyxX

SOUNDEX

Syntax
SOUNDEX(char )
It will return a character string containing the phonetic representation of char. This function permits words which are spelled differently but it sound alike in English to be compared.

Example
SELECT ename FROM emp WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE')
ENAME
----------
SMITH

SUBSTR

Syntax
 
SUBSTR(char,m [,n])
It will return a portion of char, starting at character m, n characters long. ORACLE counts from the starting of char to search the first character if m is positive. ORACLE counts backwards from the end of char if m is negative. The value of m cannot be 0. ORACLE returns all characters till the end of char if n is omitted. The value of n cannot be less than 1.

Example
SELECT SUBSTR('ABCDEFG',3,2) "Substring" FROM DUAL
Substring
----------
CD
SELECT SUBSTR('ABCDEFG',-3,2) "Substring" FROM DUAL
Substring
------------------
EF

SUBSTRB

Syntax
SUBSTRB(char,m [,n])
The similar as SUBSTR, except in whjich the arguments m and n are expressed in bytes, rather than in characters. SUBSTRB is equal to SUBSTR for a single-byte database character set.
Example
Suppose a character set in double-byte database:
SELECT SUBSTRB('ABCDEFG',5,4) "Substring with bytes" FROM DUAL
Substring with bytes
--------------------
CD

TRANSLATE

Syntax
 
TRANSLATE(char,from,to)
It will return  char  with  all  occurrences  of  from  character  replaced  through  its  corresponding  to character. The Characters in char which are not in from character are not replaced. The argument from can hold more characters than to. With that case the extra characters at the end of from have no corresponding characters in to. They are removed from the return value if these extra characters appear in char. We cannot use empty string for to in order to erase all characters in from the return value. The ORACLE interprets the empty string as null and it returns null if this function has a null argument.

Example
This statement translates the given word known as ‘Miles’ to ‘Tiles’.
SELECT TRANSLATE(‘Miles’,’M’,’T’) "Translate example" FROM DUAL
Translate example
-----------------
Tiles
This  statement is returns  a  license  number  with  the  characters  removed  and  the  digits remaining:
SELECTTRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example" FROM DUAL
Translate example
----------------
2229

UPPER

Syntax
UPPER(char)
Returns char, with all letters uppercase. The return value has the similar datatype as the argument char.
Example
SELECT UPPER('Large') "Uppercase" FROM DUAL#for selecting a uppercase of large word from dual table#
Uppercase
---------
LARGE

LOWER

Syntax
LOWER(char)
It will return char, with all letters in the lowercase. The return value has the similar datatype as the argument char (CHAR or VARCHAR2).

Example
SELECT LOWER('MR. SAMUEL HILLHOUSE')  "Lowercase" FROM DUAL
Lowercase
--------------------
mr. samuel hillhouse
Date Functions
The Date functions operate on values of the Date datatype. Whole Date functions return a value of a DATE datatype and except the MONTHS_BETWEEN function which returns a number.

ADD_MONTHS

Syntax
ADD_MONTHS(d,n)
It will return the date d plus n months. The argument n can be any integer. The day component of d if d is the last day of the month or if the resulting month has fewer days then the result is the last day of the resulting month. Or else, the result has the similar day component as d .

Example
SELECT ADD_MONTHS(sysdate,1) "Next month" FROM dual;
Next mont
---------
04-JAN-01

LAST_DAY

Syntax
LAST_DAY(d)
It will return the date of the last day of the month which contains d. This function is used to determine how many days are left in the current month.

Example
SELECT SYSDATE,LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL
SYSDATE      Last            Days Left
--------     --------- ------
04-DEC-00    31-DEC-00    27
SELECT ADD_MONTHS(LAST_DAY(sysdate),5) "Five months" FROM dual;
Five mont
--------
31-MAY-01