These functions operate on single rows only and return one result per row. There are different types of
Single-row functions are used to manipulate data items. They accept one or more arguments and
return one value for each row returned by the query. Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested.
function_name [(arg1, arg2,...)]
function_name is the name of the function.
arg1, arg2 is any argument to be used by the function. This can be represented by a
column name or expression.
Types of Single Row Function
1) Character functions:- Accept character input and can return both character and number values.
They are of two types: -
a) Case-manipulation functions. EX:- LOWER, UPPER, INITCAP
Queries that shows the use of case-manipulation functions:-
SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;
The above query used the two case-manipulation functions i.e. UPPER(last name) which shows all the last name in uppercase and LOWER(jobid) which shows all job id in lower case. The result of the above query is like this:-
Column name = EMPLOYEE DETAILS
and rows are like this The job id for JOSHI is android_developer.
Suppose you want to display the employee number, first name and department name of the employee whose last name is "joshi" and you don't its case either it is in lower case or in upper case, then use this query to fetch the result
SELECT employee_id, last_name, department_id
WHERE LOWER(last_name) = joshi;
2) Character-Manipulation Functions
a) CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)
CONCAT(Hello, World) => HelloWorld
b) SUBSTR: Extracts a string of determined length
SUBSTR(HelloWorld,1,5) => Hello
c) LENGTH: Shows the length of a string as a numeric value
LENGTH(HelloWorld) => 10
d) INSTR: Finds numeric position of a named character
INSTR(HelloWorld, W) => 6
e) LPAD: Pads the character value right-justified
Example: LPAD(salary,10,a) => aaaaa24000
f) RPAD: Pads the character value left-justified Example: RPAD(salary, 10, a) => 24000aaaaa g) TRIM: Trims heading or trailing characters (or both) from a character string (If
trimcharacter or trimsource is a character literal, you must enclose it in single quotes.) Example:
TRIM(H FROM HelloWorld) => elloWorld
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, a) "Contains a?"
WHERE SUBSTR(job_id, 4) = REP;
The above query displays employee first names and last names joined together, the length of the
employee last name, and the numeric position of the letter a in the employee last name for all
employees who have the string REP contained in the job ID starting at the fourth position of the job
Hope you like this blog :). Rest of the single value functions will be covered in next blog.
References: Oracle docs