Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

Home » Oracle » Oracle Sql » Single row functions in Oracle sql

Single row functions in Oracle sql

by 2 Comments

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

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

Single row functions in Oracle sql
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.

Reader Interactions

Comments

  1. Beata says

    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?

    Reply

    • techgoeasy says

      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

      Reply

Leave a Reply

Your email address will not be published.