SQL Character Functions

A character or string function is a function which takes one or more characters as parameters and returns a character or or number value.

SQL character functions are:

Functions	Syntax
lower()
initcap()
upper()
concat()	all characters of a
ltrim()	The SQL TRIM()
rtrim()
trim()
substr()	all characters of a
instr()	The SQL TRIM()
length()
lpad()
rpad()	all characters of a
replace()	The SQL TRIM()
translate()	all characters of a
chr()	The SQL TRIM()
ascii()	The SQL TRANSLATE()

SQL Functions

Oracle has many built-in function which help us to perform complex  operations easily and quickly. We can see  built-in function by running below SQL.

SELECT DISTINCT object_name, package_name 
FROM all_arguments
WHERE package_name = 'STANDARD';

SQL Function Type:

• Single Row Functions(or scalar functions):  These functions return a single result for every row of a query table or view(this functions appear in SELECT,WHERE and ORDER BY).

• Group Functions: These functions manipulate groups of rows and return one result per group of rows(this functions appear in SELECT and HAVING clauses).

Use of DUAL table in Oracle

This is a single column and single row dummy table provided by oracle. This is used to perform mathematical calculations without using a table.

Single Row Functions are

1. Character Functions:  Input as character and out put as both character or number values.
2. Numeric Functions:  Input as numeric and output as numeric values.
3. Date Functions:  Input as date data type and out put as both date or number.
4. Conversion Functions: Convert a value from one data type to another data type in run time.

Group Function or Aggregate Function:

• Group Function return a single row based on groups of rows
• Group Function operate on sets(the whole table or the table split into groups) of rows to give one result per group.
• Group Function can appear in SELECT and HAVING clause.

Group Functions Are:

1. AVG 
2. SUM 
3. MAX 
4. MIN 
5. COUNT 
6. STDDEV 
7. VARIANCE 

Use Of Group Function:

• All group function except COUNT ignore NULL values.
• Group function declare in SELECT list and other columns can be declare but other columns(non functional columns) should be in GROUP BY clause.

More Practice      SQL Function Questions   

Trim Function

TRIM heading  or trailing  characters or both from the character string.
LEADING trim the leading characters.
TRAILING trim trailing  characters.
BOTH OR trim both leading or trailing  characters.

Syntax: TRIM(CHAR,SET)
Example:

Use of LTRIM Function
SELECT 'xyzXxyORACLE' OrgStr,LTRIM('xyzXxyORACLE', 'xzyX') Ltrim FROM DUAL;
SELECT LTRIM(' Oracle') FROM DUAL;  --trimming space

Rtrim Function

Trimming of trailing characters from a character string.
The rightmost characters that appear in the set are removed.

Syntax: RTRIM(CHAR,SET)
Example:

Use of RTRIM Function
SELECT 'BROWNINGyxXxy' String, RTRIM('BROWNINGyxXxy', 'xyX') Rtrim FROM DUAL;
SELECT  'BROWNING ' String,RTRIM('BROWNING ') Rtrim FROM DUAL --trimming space

Ltrim Function

Trimming of heading characters from a character string.
The leftmost characters that appear in the set are removed.

Syntax: LTRIM(CHAR,SET)
Example:

Use of LTRIM Function
SELECT 'xyzXxyORACLE' OrgStr,LTRIM('xyzXxyORACLE', 'xzyX') Ltrim FROM DUAL;
SELECT LTRIM(' Oracle') FROM DUAL;  --trimming space

Concat Function

This function concatenates the first and second character value(accept only two parameters).

Syntax: CONCAT(column1,column2)
Example:

Use of CONCAT Function
SELECT 'Oracle' AS String1, 'Corporation' AS String2,CONCAT('Oracle', CONCAT(' ', 'Corporation')) AS Concat FROM DUAL;

Initcap Function

It converts Upper case for the first letter of each word, keeping all other letters as lower case.

Syntax: INITCAP(column/expression)
Example:

Use of INITCAP Function
SELECT 'oracle corporation'  AS String, INITCAP('oracle corporation') AS InitCap FROM DUAL;
SELECT 'oracleinq blogspot in' AS String,INITCAP( 'oracleinq blogspot in') AS InitCap FROM DUAL;

Upper Function

This function converts all characters into upper case.
The return value has the same data type as argument CHAR or VARCHAR2 type

Syntax: UPPER(column/expression)
Example:

Use of UPPER Function
SELECT 'oracle' AS String, UPPER ('oracle') AS UPPER FROM DUAL;
SELECT Ename||' is Designated As '||LOWER(Job) FROM EMP WHERE Job = 'MANAGER';
SELECT Ename||' is Designated As '||LOWER(Job) Ename FROM Emp WHERE Job = UPPER('manager');

Lower Function

This function converts all characters into lower case.
The return value has the same data type as argument CHAR or VARCHAR2 type

Syntax: LOWER(column/expression)
Example:

Use of LOWER Function
SELECT 'ORACLE' AS String, LOWER ('ORACLE') AS LOWER FROM DUAL;
SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate FROM EMP;
SELECT LOWER(Ename)||' is Working As '||Job Employee FROM EMP;
SELECT LOWER(Ename)||' is Working As '||Job Employee FROM EMP WHERE LOWER(Job) = 'manager';