Last updated on March 9th, 2019 at 06:15 am
This is part of series on how to learn 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;
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
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
Explicit type Conversion Functions
TO_CHAR -converts number or date to Varchar2
TO_NUMBER : Convert character to number
TO_DATE: It converts to date
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