Exam Topic: Using Single-Row Functions to Customize Output – Describe various types of functions available in SQL

Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has several built-in single row functions that work on most datatypes. Functions can take zero to many parameters as an input but it only returns one value, such as UPPER(‘test’) returns ‘TEST’. The following is a closer look at character functions that return numeric values.

Character Functions Returning Numeric Values

The following functions can take any character data type. These functions can often be seen used in the character functions returning character values standing in for the numeric portions of functions.

ASCII Returns the decimal number of the character string. Check out this table for a listing of all characters and their decimal values. Let’s say you want to check a character to make sure it is an uppercase letter. The following returns a 1:
select case when ascii('A') between 65 and 90 then 1 else 0 end from dual;
Whereas this will return a 0:
select case when ascii('a') between 65 and 90 then 1 else 0 end from dual;
INSTR Returns the first position of a substring within a string. The following returns 2 as e in the second position:
select instr('tester','e') from dual;
You can also start at a different position should you want to begin your search elsewhere in the string. The following returns 5:
select instr('tester','e',3) from dual;
If you indicate a negative position, then instr will count backward from the last character and then search backwards for the position. It’s a reverse INSTR. The following returns 5:
select instr('tester','e',-1) from dual;
But this will return 2:
select instr('tester','e',-3) from dual;
LENGTH Returns the length of the string. The following returns 6.
select length('tester') from dual;
REGEXP_COUNT  Returns the number of times a substring is found within a string.
REGEXP_INSTR  Allows searching like INSTR by using a regular expression pattern.