This is part of Oracle SQL tutorial and has good examples,explanation on single row functions in Oracle Sql
Functions are programs that take zero or more arguments and return a single value.
Functions can be single row ,multiple row functions
We will be discussing here Single row functions in SQL
Table of Contents
What is single row functions in Oracle SQL
Single-row functions are built-in functions and include character, numeric, date, conversion and user-defined functions.
All single row functions can be used in SQL or PL/SQL programs and can be used in the SELECT, WHERE and ORDER BY clauses.
Single-row functions include TO_CHAR, TO_DATE, UPPER, LOWER etc
Single-row functions can be used also in the SET clause of and UPDATE command. Single-row functions cannot be used in a HAVING clause.
Lets examine the various important single row functions
Character Functions in SQL
The Character Functions are:
LOWER(‘char’) :convert string to lowercase
SQL> select ename from "EMP"; ENAME ---------- Blake Clark Scott SQL> select lower(ename) from "EMP"; LOWER(ENAM ---------- blake clark scott
UPPER(‘CHAR’) : convert string to Upper case
SQL> select upper(ename) from "EMP"; UPPER(ENAM ---------- BLAKE CLARK SCOTT
INITCAP(‘Char’) :return a string with first letter of each word in uppercase
SQL> select initcap(ename) from "EMP"; INITCAP(EN ---------- Blake Clark Scott
CONCAT – Function has a two parameter limit.
SQL> select concat(ename,DEPTNO) from emp;
CONCAT(ENAME,DEPTNO)
--------------------------------------------------
Blake10
Clark10
Scott20
SQL> select concat(ename,empno,DEPTNO) from emp;
select concat(ename,empno,DEPTNO) from emp
*
ERROR at line 1:
ORA-00909: invalid number of arguments
LENGTH :return the length of string
SQL> select ename,length(ename) from emp; ENAME LENGTH(ENAME) ---------- ------------- Blake 5 Clark 5 Scott 5
LENGTHB: returns the length of a string in bytes
SQL> select ename,length(ename),lengthb(ename) from emp; ENAME LENGTH(ENAME) LENGTHB(ENAME) ---------- ------------- -------------- Blake 5 5 Clark 5 5 Scott 5 5
INSTR :returns the index of searched string within a string,
SQL> select ename,instr(ename,'A') from emp; ENAME INSTR(ENAME,'A') ---------- ---------------- Blake 0 Clark 0 Scott 0 SQL> select ename,instr(ename,'a') from emp; ENAME INSTR(ENAME,'A') ---------- ---------------- Blake 3 Clark 3 Scott 0
LPAD: left pad a string with n characters
SQL> select ename,lpad(ename,12) from emp; ENAME LPAD(ENAME,12) ---------- ------------------------------------ Blake Blake Clark Clark Scott Scott SQL> select ename,lpad(ename,12,'-') from emp; ENAME LPAD(ENAME,12,'-') ---------- ------------------------------------ Blake -------Blake Clark -------Clark Scott -------Scott
LTRIM: strip leading characters from a string
SQL> select ltrim(' name') from dual;
LTRI
----
name
SQL> select ltrim(' name ') from dual;
LTRIM('
-------
name
SQL> select LTRIM('000123', '0') from dual;
LTR
---
123
SQL> select LTRIM('1000123', '0') from dual;
LTRIM('
-------
1000123
REPLACE: perform a substring search and replace
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes
--------------
BLACK and BLUE
SUBSTR – return a section of string specified in numeric values
SUBSTRB – same as SUBSTR only with bytes
SOUNDEX – return a phonetic representation of a string
TRANSLATE – perform character search and replace
TRIM – string leading and trailing characters of string
Examples select emp_name, concat(emp_name,dept_name), length (ename),INSTR(ename, 'A') from dept; select lower(emp_name) from employee; select upper(first_name) from emp;
As explained ,the single row function works on each rows
Related: Oracle sql decode processing
Numeric functions in Sql
The Number Functions are:
ROUND :rounds a number
SQL> select round(3.456) from dual;
ROUND(3.456)
------------
3
SQL> select round(3.456,2) from dual;
ROUND(3.456,2)
--------------
3.46
TRUNC :truncates a number
MOD:returns the modulus.i.e the remainder
SQL> select mod(4,2) from dual;
MOD(4,2)
----------
0
SQL> select mod(4,3) from dual;
MOD(4,3)
----------
1
SIGN:return positive, negative or zero
CEIL – returns the next higher number
SQL> select ceil(3.1) from dual;
CEIL(3.1)
----------
4
SQL> select ceil(3) from dual;
CEIL(3)
----------
3
LOG – return the logarithm
FLOOR – return the next smaller number
SQL> select floor(3.1) from dual;
FLOOR(3.1)
----------
3
SQL> select floor(3) from dual;
FLOOR(3)
----------
3
SQRT – return the square root of a number
Examples SELECT SQRT(25) "Square root" FROM DUAL; SELECT order_total, CEIL(order_total) FROM orders WHERE order_id = 58758;
Oracle Date Functions
The Oracle Date Functions are:
SYSDATE :return current date
DUAL
MONTHS_BETWEEN (Returns a number) :return number of month between two dates
ADD_MONTHS :add a number of months to date
NEXT_DAY :returns the next day of week following a given date
LAST_DAY :return last day of month
DBTIMEZONE – return the database time zone
TRUNC: truncates a date.
CURRENT_TIMESTAMP – returns the date and time in TIMESTAMP format
Examples SQL> select CURRENT_TIMESTAMP from dual; CURRENT_TIMESTAMP --------------------------------------------------------------------------- 01-SEP-19 04.33.26.493097 PM +00:00 SQL> select sysdate from dual; SYSDATE --------------- 01-SEP-19
Explicit type Conversion Functions
TO_CHAR -converts number or date to VARCHAR2
TO_NUMBER : Convert character to number
TO_DATE: It converts to date
Examples SQL> select to_char(sysdate ,'DD-MON-YYYY,MI') from dual 2 ; TO_CHAR(SYSDATE,'DD- -------------------- 01-SEP-2019,34
Other Functions
NVL (expr1, expr2) and NVL3
NVL – takes two arguments: NVL(x1, x2), Returns x2 when x1 is NULL or x1 when x1 is not NULL. NVL2 – takes three arguments: NVL2(x1, x2, x3), returns x3 when x1 is NULL or x2 when x1 is not NULL
DECODE This function works like a IF-THEN-ELSE or CASE statement.
COALESCE – return the first non-NULL value in a list
SYS_CONTEXT – return system attributes
UID – return numeric user ID for current session
Small quiz to test the knowledge on Single row functions in SQL
Question 1: You are using single-row functions in a select statement. Which function can best be categorized as similar in function to an if-then-else statement?
A) sqrt( )
B) decode( )
C) rowidtochar( )
D) new_time( )
Explanation:
The decode( ) function acts like an if-then-else clause in your SQL statements. Choice sqrt( ) is incorrect because sqrt( ) produces the
square root of a number. Choice new_time( ) is incorrect because the new_time( ) function returns a new time based on values specified in the call to that function.
Finally, choice rowidtochar( ) is incorrect because rowidtochar( ) is a function that converts ROWID information to CHAR information.
Question 2:
You want to use single-row functions in your SQL statements. Which three of the following are number functions? (Choose three of the four.)
A) sinh( )
B) sqrt( )
C) to_number( )
D) round( )
Explanation:
The only non-number function in this list is the to_number( ) function,
which is a conversion operation.
Question 3 The following SQL statement was taken from a SQL*Plus session:
select decode(EMPNO, 74765, ‘INACTIVE’, ‘ACTIVE’) empno from DEPT
where substr(ENAME,1,1) > to_number(‘V’) and EMPNO > 1000
order by EMPNO desc, ENAME asc;
Which of the following lines in the select statement shown in the
previous code block contain an error?
A) select decode(EMPNO, 58385, ‘INACTIVE’, ‘ACTIVE’) empno
B) from EMP
C) and EMPNO > 02000
D) where substr(ENAME,1,1) > to_number(‘S’)
E) order by EMPNO desc, ENAME asc;
Explanation: (D)
Characters that are alphabetic, such as S, cannot be converted into numbers. When this statement is run, it will produce an error on this line. The
other lines in this query are correct as composed.
Question 4 Which SELECT statement will get the result ‘elloworld’ from the string ‘HelloWorld’?
A. SELECT SUBSTR( ‘HelloWorld’,1) FROM dual;
B. SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual;
C. SELECT LOWER(SUBSTR(‘HelloWorld’, 1, 1) FROM dual;
D. SELECT LOWER(SUBSTR(‘HelloWorld’, 2, 1) FROM dual;
E. SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;
Answer: E
Hope you like this post on Single row functions in SQL. Please do provide the feedback
Related Articles
Analytic functions in oracle : Oracle Analytic functions compute an aggregate value based on group of rows by using over partition by oracle clause , they differ from aggregate functions
rank in oracle : RANK, DENSE_RANK and ROW_NUMBER are oracle analytical function which are used to rank rows in the group of rows called window
Lead function in oracle : Check out LAG function in Oracle & Lead function in Oracle, how to use them in analytic queries and how it works in Oracle sql
Top-N Queries in Oracle :Check out this page on exploring the various ways to achieve Top-N Queries in Oracle and Pagination in oracle query oracle database.
Single row function in ORDER BY clause seems to not working for me. Please see below example.
select * from first_table order by trunc(1.8) desc;
RESULT:
COL1 COL2
1 5
2 5
Do you know the reason?
hi
You have to choose actual column in order for it to work
SQL> select NODE_NAME from test_nodes order by 1 desc;
NODE_NAME
XYK
XYZ
AUTHEN
SQL> select NODE_NAME from test_nodes order by length(NODE_NAME) desc;
NODE_NAME
AUTHEN
XYK
XYZ
SQL> select NODE_NAME from test_nodes order by length(NODE_NAME) asc
2 ;
NODE_NAME
XYK
XYZ
AUTHEN