• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Techgoeasy

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

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
  • New? Start Here
  • About Us
  • Hire me
  • Contact Us

Single row functions in Oracle sql

August 14, 2019 by techgoeasy Leave a Comment

This is part of 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

Filed Under: Oracle, Oracle Sql Tagged With: Single-row functions in sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Top 40 oracle dba interview questions and answers
  • Oracle Indexes and types of indexes in oracle with example
  • 40 Important Weblogic Interview questions
  • Top 30 Most Useful Concurrent Manager Queries
  • 21 awk command in Unix/Linux




Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Top 40 Oracle RAC interview Questions
  • How to change date format in oracle database
  • How to Create SSH Tunneling or Port Forwarding in Linux to connect with Oracle database on Cloud
  • How to give oracle kill own session privilege to Developers
  • How to download Patch from Oracle using Wget

ORA-errors

  1. ORA-06512
  2. ORA-00911
  3. ORA-03113
  4. ORA-29913
  5. ORA-00257
  6. ORA-00054
  7. ORA-27154
  8. ORA-29285
  9. ORA-29280
  10. ORA-29283
  11. ORA-01017
  12. ORA-00900
  13. ORA-00001
  14. ORA-00936
  • Privacy Policy

Copyright © 2019 : TechGoEasy