Using Single-Row Functions to Customize Output
Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query. An argument can be one of the following:
• User-supplied constant
• Variable value
• Column name
• Expression
Features of single-row functions include:
• Manipulate data items
• Accept arguments and return one value
• Act on each row that is returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments that can be a column or an expression
Character Functions
SQL> select lower('AbC DeF'),upper('AbC DeF'),initcap('ABc DEf') from dual;
LOWER('ABCDEF' UPPER('ABCDEF' INITCAP('ABCDE
-------------- -------------- --------------
abc def ABC DEF Abc Def
SQL> select concat('ab','cd') from dual;
CONCAT('
--------
abcd
SQL> select concat('ab','cd','ef') from dual;
select concat('ab','cd','ef') from dual
*
ERROR at line 1:
ORA-00909: invalid number of arguments
SQL> select substr('abcdef',2,1),substr('abcdef',2),substr('abcdef',-3,2),substr('abcdef',-3) from dual;
SU SUBSTR('AB SUBS SUBSTR
-- ---------- ---- ------
b bcdef de def
SQL> select length('abcdef') from dual;
LENGTH('ABCDEF')
----------------
6
SQL> select instr('abcabc','c',1,1),instr('abcabc','c',2,2),instr('abcabc','c',4),instr('abcabc','c') from dual;
INSTR('ABCABC','C',1,1) INSTR('ABCABC','C',2,2) INSTR('ABCABC','C',4)
----------------------- ----------------------- ---------------------
INSTR('ABCABC','C')
-------------------
3 6 6
3
SQL> select lpad('abcdef',10,'x'),rpad('abcdef',10,'x') from dual;
LPAD('ABCDEF',10,'X' RPAD('ABCDEF',10,'X'
-------------------- --------------------
xxxxabcdef abcdefxxxx
SQL> select lpad('abcdef',5,'x'),rpad('abcdef',5,'x') from dual;
LPAD('ABCD RPAD('ABCD
---------- ----------
abcde abcde
SQL> select trim(leading 'a' from 'abcabc'),trim(trailing 'c' from 'abcabc'),trim(both 'a' from 'abcabc') from dual;
TRIM(LEADI TRIM(TRAIL TRIM(BOTH'
---------- ---------- ----------
bcabc abcab bcabc
SQL> select trim('a' from 'abcaba'),trim(leading from ' abcabc '),trim(' abcabc ') from dual;
TRIM('A' TRIM(LEADINGFR TRIM('ABCABC
-------- -------------- ------------
bcab abcabc abcabc
SQL> select trim('xx' from 'xxabcxx') from dual;
select trim('xx' from 'xxabcxx') from dual
*
ERROR at line 1:
ORA-30001: trim set should have only one character
SQL> select replace('abcabc','a','d') from dual;
REPLACE('ABC
------------
dbcdbc
Nesting Functions
Single-row functions can be nested to any level
Numeric Functions
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-1) FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
45.92 45 40
SQL> select mod(35,6) from dual;
MOD(35,6)
----------
5
working with dates
• The Oracle Database stores dates in an internal numeric format: century, year, month, day, hours, minutes, and seconds.
• The default date display format is DD-MON-RR
• The RR date format is similar to the YY element, but you can use it to specify different centuries.
• Use the RR date format element instead of YY so that the century of the return value varies according to the specified two-digit year and the last two digits of the current year.
sysdate
SYSDATE is a date function that returns the current database server date and time. You can use SYSDATE just as you would use any other column name. For example, you can display the current date by selecting SYSDATE from a table. It is customary to select SYSDATE from a public table called DUAL.
Note: SYSDATE returns the current date and time set for the operating system on which the database resides. Therefore, if you are in a place in Australia and connected to a remote database in a location in the United States (U.S.), the sysdate function will return the U.S. date and time. In that case, you can use the CURRENT_DATE function that returns the current date in the session time zone.
Date Functions
SQL> select * from t1;
RIQI RIQI2
------------------- -------------------
2018-08-07 22:21:54 2018-08-17 20:35:48
SQL> select months_between(sysdate,riqi),add_months(riqi,1),next_day(sysdate,'MONDAY'),last_day(sysdate) from t1;
MONTHS_BETWEEN(SYSDATE,RIQI) ADD_MONTHS(RIQI,1) NEXT_DAY(SYSDATE,'M
---------------------------- ------------------- -------------------
LAST_DAY(SYSDATE)
-------------------
.711141353 2018-09-07 22:21:54 2018-09-03 23:27:15
2018-08-31 23:27:15
SQL> select sysdate,round(sysdate),trunc(sysdate) from dual;
SYSDATE ROUND(SYSDATE) TRUNC(SYSDATE)
------------------- ------------------- -------------------
2018-08-29 23:28:19 2018-08-30 00:00:00 2018-08-29 00:00:00
SQL> select sysdate,round(sysdate,'month'),trunc(sysdate,'month') from dual;
SYSDATE ROUND(SYSDATE,'MONT TRUNC(SYSDATE,'MONT
------------------- ------------------- -------------------
2018-08-29 23:29:52 2018-09-01 00:00:00 2018-08-01 00:00:00