Single-row functions are operation performed on one and only one row of a table. This is in contrast to multi-row functions which operate on more than on row and return aggregate information.
Describe various types of functions that are available in SQL
Null values functions, such as NVL.
Character functions such as length, concat, and upper.
Number functions such as sin, ceil, and abs.
Date functions such as sysdate, last_day, and next_day
Conversion functions such as to_date, to_char, and unistr
Other functions, usually technical or if/then/else in nature such as to_clob, or decode
Use character, number, and date functions in SELECT statements
Character, number and date functions can be used in a select statement to both modify the output of the rows/columns selected and to modify the rows selected via the where clause.
Character Examples:
select ascii('a') from dual; --Get the ascii code for a string
select chr('97') from dual; --Turn a ascii code into a string
SELECT LPAD('Dan Morgan', 25, 'x') FROM dual; --Left pad a value with the letter x
select lower(table_name) from user_tables; --Return lower case values also try upper()
select trim(table_name) from user_tables; --remove leading and ending white space try RTrim and LTrim
SELECT INSTR('Find me if you can', 'me ', 1, 1) FROM dual; --Returns me loc, 0 if not found, optional start pos and occurance number
SELECT substr('Return foo please', 8, 3) FROM dual; -- return 'foo', give initial position and number of characters to return
Number Examples:
TODO
Date Examples:
To_date: SELECT * FROM all_tables WHERE last_analyzed > to_date('10/16/2008 1:23:33 PM', 'MM/DD/YYYY HH:MI:SS PM');
Note the date format codes parameter.
Current date: SELECT SYSDATE FROM dual;
Combo last day in month: SELECT trunc(last_day(sysdate), 'DD') FROM dual;
This returns the last day of the current month and truncate the sysdate to just return day granularity
Round to nearest hour: SELECT round(sysdate, 'HH') FROM dual;
Convert timezone: SELECT new_time(sysdate, 'EST', 'GMT') FROM dual;
Describe the use of conversion functions
TODO