close

Conversion Functions

Function Name
 Usage EXAMPLE
TO_CHAR Converts the other datatype to character datatype TO_CHAR(123);
TO_DATE ( string, format ) Converts the given string to date. The string should match with the format. TO_DATE('2015-JAN-15', 'YYYY-MON-DD');
Output: 1/15/2015
TO_NUMBER (text, format) Converts the text to number type of the given format. Informat '9' denotes the number of digits Select TO_NUMBER('1234','9999') from dual;
Output: 1234
　
Select TO_NUMBER('1,234.45','9,999.99') from dual;
Output: 1234

 

String Functions

Function Name
 Usage EXAMPLE
INSTR(text, string, start, occurance) Gives the position of particular text in the given string.
   text – Main string
   string – text that need to be searched
   start – starting position of the search (optional)
   accordance – occurrence of the searched string (optional)		 Select INSTR('AEROPLANE','E',2,1) from dual
Output: 2
 Select INSTR('AEROPLANE','E',2,2) from dual
Output: 9 (2nd occurance of E)
SUBSTR ( text, start, length) Gives the substring value of the main string.
  text – main string
  start – starting position
  length – length to be sub stringed		 select substr('aeroplane',1,7) from dual
Output: aeropla
UPPER ( text ) Returns the uppercase of the provided text Select upper('guru99') from dual;
Output: GURU99
LOWER ( text ) Returns the lowercase of the provided text Select lower ('AerOpLane') from dual;
Output: aeroplane
INITCAP ( text) Returns the given text with the starting letter in upper case. Select ('guru99') from dual
Output: Guru99
Select ('my story') from dual
Output: My Story
LENGTH ( text ) Returns the length of the given string Select LENGTH ('guru99') from dual;
Output: 6
LPAD ( text, length, pad_char) Pads the string in the left side for the given length (total string) with the given character Select LPAD('guru99', 10, '$') from dual;
Output: $$$$guru99
RPAD (text, length, pad_char) Pads the string in the right side for the given length (total string) with the given character Select RPAD('guru99',10,'-') from dual
Output: guru99----
LTRIM ( text ) Trims the leading white space from the text Select LTRIM(' Guru99') from dual;
Output: Guru99
RTRIM ( text ) Trims the trailing white space from the text Select RTRIM('Guru99 ') from dual;
Output; Guru99

Date Functions

Function Name
 Usage EXAMPLE
ADD_MONTHS (date, no.of months) Adds the given months to the date ADD_MONTH('2015-01-01',5);
Output: 05/01/2015
SYSDATE Returns the current date and time of the server Select SYSDATE from dual;
Output: 10/4/2015 2:11:43 PM
TRUNC Round of the date variable to the lower possible value select sysdate, TRUNC(sysdate) from dual; Output: 10/4/2015 2:12:39 PM 10/4/2015
ROUND Rounds the date to the nearest limit either higher or lower Select sysdate, ROUND(sysdate) from dual Output: 10/4/2015 2:14:34 PM 10/5/2015
MONTHS_BETWEEN Returns the number of months between two dates Select MONTHS_BETWEEN (sysdate+60, sysdate) from dual
Output: 2

 

REF : https://www.guru99.com/

 

arrow
arrow
    文章標籤
    plsql
    全站熱搜

    JosephChou 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄