Oracle SQL in 10 Minutes - Asim Abbasi

Chapter 3: SQL Built-in Functions

Page #: 1 | 2 | 3 | 4 | 5

Figure 6: SQL Functions: Single Row & Group Functions.
________________________________________

SQL language like all the other computer languages comes with many pre-built functions. These functions can be categorized into two major groups. One is called “Single Row” functions and the other one is called “Group” functions. Single Row function as the name says gets implemented on single records whereas Group functions get implemented on the multiple records. These functions will get clarified in your mind once we start discussing one by one.

Single Row Functions:

Single Row functions are further subdivided into five categories viz., Character, Data, Numeric, Conversion & other functions. First we will start with Character Functions or more precisely “Single Row Character Functions.”

Character Functions

Following are the functions that fall under this category.

CHR
LTRIM
INSTR
ASCII
RTRIM
INSTRB
CONCAT
TRIM
LENGTH
INITCAP
REPLACE
LENGTHB
LOWER
SOUNDEX

UPPER
SUBSTR

LPAD
SUBSTRB

RPAD

Example:

SELECT CHR(67)||CHR(65)||CHR(84) "Pet"
FROM DUAL;

Output:

Pet
---
CAT

Example:

SELECT ASCII('Q')
FROM DUAL;

Output:

ASCII('Q')
----------
81

Example:

SELECT CONCAT(ename, ' is a good boy') "Result"
FROM emp
WHERE empno = 7900;

Output:

Result
-----------------
JAMES is a good boy

Example:

SELECT INITCAP('the king') "Capitals"
FROM DUAL;

Output:

Capitals
---------
The King
Example:

SELECT LOWER('THE KING') "Lowercase"
FROM DUAL;

Output:

Lowercase
-------------
the king

Similarly we can use the UPPER function.

Example:

SELECT LPAD('Page 1',15,'*+') "LPAD example"
FROM DUAL;

Output:

LPAD example
---------------
*+*+*+*+*Page 1

Similarly we can use RPAD.

Example:

SELECT LTRIM('121SPIDERMAN','12') "Result"
FROM DUAL;

Output:

Result
--------
SPIDERMAN

Similarly we can use RTRIM.

Example:

SELECT TRIM (0 FROM 001234567000) Result"
FROM DUAL;

Output:

Result
--------
1234567

SELECT TRIM (LEADING 0
FROM 001234567000) Result"
FROM DUAL;

Output:

Result
--------
1234567000

Similarly we can replace LEADING with TRAILING to omit trailing zeros in 001234567000 and the output will then be 001234567.

Example:

SELECT REPLACE('KING KONG','KI','HO') Result"
FROM DUAL;

Output:

Changes
--------------
HONG KONG

Example:

SELECT ename
FROM emp
WHERE SOUNDEX(ename) = SOUNDEX('SMYTHE');

Output:

ENAME
----------
SMITH

This function allows you to compare words that are spelled differently, but sound alike in English. You must have noticed that if you do a search in google (www.google.com) using wrong spelling e.g. I made a wrong spelled word “Neus” search on google and it came up with, “Did you mean News?”. That is basically the beauty of this function.

Figure 7: Google’s implementation of SOUNDEX function. ________________________________________

Example:

SELECT SUBSTR('SPIDERMAN',7,3) "Result"
FROM DUAL;
Output:

Result
---------
MAN

Similarly we can use SUBSTRB; for a single-byte database character set, SUBSTRB is equivalent to SUBSTR. Floating-point numbers passed as arguments to SUBSTRB are automatically converted to integers. Assume a double-byte database character set:

SELECT SUBSTRB(' SPIDERMAN',7,4.3) "Result"
FROM DUAL;

Output:

Result
--------
DE

Example:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"
FROM DUAL;

Output:

Result
----------
14

Similarly we can use INSTRB; for a single-byte database character set, INSTRB is equivalent to INSTR. Lets suppose a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2)
"Result"
FROM DUAL;

Output:

Result
--------
27

Example:

SELECT LENGTH('SPIDERMAN') "Result"
FROM DUAL;

Output:

Result
--------
9

Similarly we can use LENGTHB; for a single-byte database character set, LENGTHB is equivalent to LENGTH. Lets suppose a double-byte database character set.

SELECT LENGTHB ('SPIDERMAN') "Result"
FROM DUAL;

Output:

Result
--------
14

Date Functions:

Following functions fall under this category.

ADD_MONTHS
MONTHS_BETWEEN
LAST_DAY
ROUND
SYSDATE
TRUNC

Example:

SELECT ADD_MONTHS(hiredate,1)
FROM emp
WHERE ename = 'SMITH';

Example:

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

Output:

SYSDATE Last Days Left
--------- --------- ----------
23-OCT-97 31-OCT-97 8

Example:

SELECT MONTHS_BETWEEN(SYSDATE, hiredate) “Months of Service"
FROM DUAL;

Example:

SELECT ROUND (TO_DATE ('27-OCT-92'),'YEAR')
"New Year" FROM DUAL;

Output:

New Year
---------
01-JAN-93
Example:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
"New Year" FROM DUAL;

Output:

New Year
---------
01-JAN-92

Chapter 3: SQL Built-in Functions

Page #: 1 | 2 | 3 | 4 | 5

Share with others:

products

books

FREE VB, ASP Projects

Oracle SQL in 10 Minutes - Asim Abbasi