Scribd
Upload
35 views4 pages

Single Row Functions Examples

This document contains examples of SQL functions that can be used to manipulate and format date and string values in Oracle databases. Functions demonstrated include UPPER, LOWER, INITCAP, SUBSTR, INSTR, LPAD, RPAD, ROUND, TRUNC, MOD, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_CHAR, and TO_DATE. The examples show how to use these functions to extract parts of strings, pad strings, perform calculations on dates, and format dates and numbers into strings.

Uploaded by

omarkhanfar2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
35 views4 pages

Single Row Functions Examples

This document contains examples of SQL functions that can be used to manipulate and format date and string values in Oracle databases. Functions demonstrated include UPPER, LOWER, INITCAP, SUBSTR, INSTR, LPAD, RPAD, ROUND, TRUNC, MOD, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_CHAR, and TO_DATE. The examples show how to use these functions to extract parts of strings, pad strings, perform calculations on dates, and format dates and numbers into strings.

Uploaded by

omarkhanfar2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 4

Examples:

select upper('database system'), initcap('database system'), lower('Database SYSTEM') from


dual
-------------------------------------------------------------------------------

select concat('Data', concat('System',' DB')) from dual

-------------------------------------------------------------------------------

select substr('Database', 2) from dual

select substr('Database', 2,3) from dual

select substr('Database', 9) from dual

select substr('Database', -2, 1) from dual

select substr('Database', -3) from dual

select substr('Database', -9, 2) from dual

-------------------------------------------------------------------------------

select instr('Database', 'a', 1) from dual

select instr('Database', 'a', -1) from dual

select instr('Database', 'a', -3) from dual

select instr('Database', 'a', -4) from dual

select instr('This is', 'a') from dual

select instr('This is', 'is') from dual

select instr('This is', 'is', 4) from dual

select instr('This is', 'Is') from dual


-------------------------------------------------------------------------------
select lpad('Database', 10, '*') from dual

select rpad('Database', 10, '*') from dual

select lpad('Database', 8, '*') from dual

select lpad('Database', 4, '*') from dual


-------------------------------------------------------------------------------

select round(138.547, 2) from dual

select round(138.547, 1) from dual

select round(138.547, 0) from dual

select round(138.547, -1) from dual

select round(138.547, -2) from dual

select round(138.547, -3) from dual

-------------------------------------------------------------------------------

select trunc(138.547, 2) from dual

select trunc(138.547, 1) from dual

select trunc(138.547, 0) from dual

select trunc(138.547, -1) from dual

select trunc(138.547, -2) from dual

select trunc(138.547, -3) from dual

-------------------------------------------------------------------------------

select mod(1600, 300) from dual


-------------------------------------------------------------------------------

select months_between(sysdate, '23-FEB-13') from dual

-------------------------------------------------------------------------------

select add_months(sysdate, 4) from dual

select add_months(sysdate, -5) from dual

-------------------------------------------------------------------------------

select next_day(sysdate, 'sunday') from dual

-------------------------------------------------------------------------------

select last_day(sysdate) from dual

-------------------------------------------------------------------------------

select round(to_date('23-JUL-13','dd-MON-yy'), 'MONTH') from dual

select round(to_date('23-JUL-13','dd-MON-yy'), 'YEAR') from dual

select round(to_date('2-JUL-13','dd-MON-yy'), 'MONTH') from dual

select round(to_date('23-FEB-13','dd-MON-yy'), 'YEAR') from dual

select round(to_date('23-DEC-13','dd-MON-yy'), 'MONTH') from dual

-------------------------------------------------------------------------------

select trunc(to_date('23-JUL-13','dd-MON-yy'), 'MONTH') from dual

select trunc(to_date('23-JUL-13','dd-MON-yy'), 'YEAR') from dual

select trunc(to_date('2-JUL-13','dd-MON-yy'), 'MONTH') from dual

select trunc(to_date('23-FEB-13','dd-MON-yy'), 'YEAR') from dual


select trunc(to_date('23-DEC-13','dd-MON-yy'), 'MONTH') from dual

-------------------------------------------------------------------------------

select to_char(6500, '$999,999') from dual

select to_char(6500, '$099,999') from dual

select to_char(6500, '$099') from dual

-------------------------------------------------------------------------------

select to_char(sysdate, 'dd "of" Month') from dual

select to_char(sysdate, '"Today is " Day, Month DD, YYYY') from dual

select to_char(sysdate, 'fmHH12:MI:SS AM') from dual

select to_char(sysdate, 'fmHH24:MI:SS') from dual

You might also like