Oracle SQL Built-in Functions

1
Oracle SQL Built-in Functions

• Chapter 5 in Lab Reference

2
Text Functions Concatenation operator

• To concatenate column names with other column
  names or with literal characters.
• Example
• Select name has an id of ssn
• From employee

• NAMEHAS AN ID OF'SSN
• --------------------------------------------------
  ----------------------------
• Jamil N.Samir has an id of 123456789
• Amani F.Zaki has an id of 999887777
• Jihan H.Walid has an id of 987654321
• Ramy S.Nabil has an id of 666884444
• Joyce A.Eman has an id of 453453453
• Ahmad V.Jabbar has an id of 987987987
• James B.Baher has an id of 888665555
• 7 rows selected.

3
Column Alias Names

• Example
• Select name AS Employee
• From employee

EMPLOYEE ------------------------------- Jamil
N.Samir Amani F.Zaki Jihan H.Walid Ramy
S.Nabil Joyce A.Eman Ahmad V.Jabbar James
B.Baher 7 rows selected.
4
Column Alias Names

• When you want to include spaces or special
  characters in alias names, then enclose the alias
  name in double quotation marks.
• Example
• Select name ' has an id of ' ssn
  "Important information"
• From employee

Important information ---------------------------
--------------------------------------------------
---- Jamil N.Samir has an id of 123456789 Amani
F.Zaki has an id of 999887777 Jihan H.Walid has
an id of 987654321 Ahmad V.Jabbar has an id of
987987987 James B.Baher has an id of 888665555 7
rows selected.
5
Table Alias Names

• Example
• Select T.item_id, T.item_desc
• From item T

• item_id item_desc
• -----------------------------------------------
  ----------
• LA-101 Box, Small
• NY-102 Bottle, Large

6
Number FunctionsROUND

• The ROUND function rounds the value you want to
  modify.
• Example
• Select product_name, product_price,
  ROUND(product_price,0) From product

product_name product_price
ROUND(product_price,0) ---------------------------
--------------------------------------------------
--------------------------------------------------
--------------- Roco Pencil
3.95 4 FABER Pen
5
5 Roco Pad 2.2
2
7
Number FunctionsTRUNC

• The TRUNC function truncates precision from a
  number.
• Example
• Select product_name, product_price,
  TRUNC(product_price,0) From product

product_name product_price
TRUNC(product_price,0)---------------------------
--------------------------------------------------
--------------------------------------------------
------------- Roco Pencil
3.95 3 FABER Pen
5
5 Roco Pad 2.2
2
8
Number FunctionsPOWER

• power(m,n)
• number m raised to the power of n.
• Example
• Select power(salary,2)
• From employee
• where ssn123456789

• POWER(SALARY,2)
• -------------------------------------------
• 900000000

9
Number FunctionsSQRT

• sqrt(n)
• returns square root of n.
• Example
• Select sqrt(salary)
• From employee
• where ssn123456789

• SQRT(SALARY)
• --------------------------------------
• 173.20508

10
Text FunctionsUPPER, LOWER INITCAP

• These three functions change the case of the text
  you give them.
• Example
• Select UPPER(product_name)
• From product
• Example
• Select LOWER(product_name)
• From product

UPPER(product_name) -------------------------
------------------------------- ROCO PENCIL

FABER PEN ROCO PAD
LOWER(product_name) -------------------------
------------------------------- roco pencil
faber pen roco pad
11

Text FunctionsUPPER, LOWER INITCAP

• Example
• Select INITCAP(product_name)
• From product

• INITCAP(product_name)
• --------------------------------------------------
  --------
• Roco Pencil
  
• Faber Pen
  Roco Pad

12
Text FunctionsLENGTH

• To determine the lengths of the data stored in a
  database column.
• Example
• Select product_name, LENGTH(product_name) AS
  Name_Length
• From Product
• where LENGTH(product_name)gt8

product_name NAME_Length
-------------------------------------------------
-------------------------------- FABER Pen
9 Roco Pencil
11

13
Text FunctionsSUBSTR

• To separate multiple bits of data into discrete
  segments.
• Example
• Select SUBSTR(item_id,1,2) Location,
  SUBSTR(item_id,4,3) Number, Item_desc
• From item

Location Number
Item_desc ----------------------------------------
--------------------------------------------------
--- LA 101
Box, Small NY 102
Bottle, Large
14
Text FunctionsREPLACE

• Replace(char, str1, str2)
• Every occurrence of str1 in char is replaced by
  str2.
• Example
• Select Replace(name,'Jamil','Sara')
• From employee

• REPLACE(NAME,'JAMIL','SARA')
• --------------------------------------------------
  -----------------
• Sara N.Samir
• Amani F.Zaki
• Jihan H.Walid
• Ramy S.Nabil
• Joyce A.Eman
• Ahmad V.Jabbar
• James B.Baher
• 7 rows selected.

15
Date Functions
Function Description Syntax
Sysdate Get current system date and time. INSERT INTO employee VALUES (, trunc(sysdate),.)
16
Data Conversion Functions
Function Description
To_char(input_value, format_code) Converts any data type to character data type.
To_number(input_value, format_code) Converts a valid set of numeric character data to number data type.
To_date(input_value, format_code) Converts character data of the proper format to date data type.