Single row functions in sql

Comment

Last updated on March 29th, 2019 at 05:59 pm

This is part of SQL tutorial and has good examples,explanation on single row functions in 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 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
UPPER(‘CHAR’)  : convert string to Upper case
INITCAP(‘Char’) :return a string with first letter of each word in uppercase
CONCAT – Function has a two parameter limit.
LENGTH :return the length of string
LENGTHB: returns the length of a string in bytes
INSTR :returns the index of searched string within a string,
LPAD:left pad a string with n characters
LTRIM:strip leading characters from a string
REPLACE: perform a substring search and replace
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
TRUNC :truncates a number
MOD:returns the modulo
SIGN:return positive, negative or zero
CEIL – returns the next higher number
LOG – return the logarithm
FLOOR – return the next smaller number
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

select sysdate from dual

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
select to_char(sysdate ,'DD-MON-YYYY,MI') from dual

 

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

Leave a Reply