Oracle Database 12c: SQL Workshop I: 04 Using Single-Row Functions to Customize Output

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