[Oracle-Sql] C.11 Selected Single-Row Functions

Date: 2023.07.24 Updated: 2023.07.24

Categories: Oracle-Sql

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

Reference link - SQL Oracle Function: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm

Terminology

Function – predefined block of code that accepts arguments
Single-row function – returns one row of results for each record processed
Multiple-row function – returns one result per group of data processed

Types of Functions

Type of Function	Functions
Case conversion functions	UPPER, LOWER, INITCAP
Character manipulation functions	SUBSTR, INSTR, LENGTH, LPAD/RPAD, LTRIM/RTRIM, REPLACE, TRANSLATE, CONCAT
Numeric functions	ROUND, TRUNC, MOD, ABS, POWER
Date functions	MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE, ROUND, TRUNC, CURRENT_DATE
Regular expressions	REGEXP_LIKE, REGEXP_SUBSTR
Other functions	NVL, NVL2, NULLIF, TO_CHAR, DECODE, CASE expression, SOUNDEX, TO_NUMBER

Case Conversion Functions

Case conversion functions alter the case of data stored in a column or character string

Used in a SELECT clause, they alter the appearance of the data in the results
Used in a WHERE clause, they alter the value for comparison

LOWER Function

Used to convert characters to lowercase letters

SELECT LOWER(FIRSTNAME), LOWER(LASTNAME)
  FROM CUSTOMERS
  WHERE LOWER(LASTNAME) = 'NELSON';

UPPER Function

Used to convert characters to uppercase letters
It can be used in the same way as the LOWER function
- To affect the display of characters, it is used in a SELECT clause
- To modify the case of characters for a search condition, it is used in a WHERE clause
The syntax for the UPPER function is UPPER(c)
- Where c is the character string or field to be converted into uppercase characters

INITCAP Function

Used to convert characters to mixed case

SELECT INITCAP(FIRSTNAME) "FIRST NAME", INITCAP(LASTNAME) "LAST NAME"
  FROM CUSTOMERS
  WHERE LASTNAME = 'NELSON';

Character Manipulation Functions

Character manipulation functions manipulate data by extracting substrings, counting the number of characters, replacing strings, etc

SUBSTR Function

Used to return a substring, or portion of a string

SELECT DISTINCT ZIP, SUBSTR(ZIP, 1, 3), SUBSTR(ZIP, -3, 2)
  FROM CUSTOMERS
  WHERE SUBSTR(ZIP, -3, 2) < 30;

INSTR Function

SELECT NAME, INSTR(NAME, ',') "FIRST COMMA",
             INSTR(NAME, ',', 10) "START READ POSITION 10",
             INSTR(NAME, ',', 1, 2) "SECOND COMMA"
  FROM CONTACTS;

Nesting Function

SUBSTR(NAME, INSTR(NAME, ',')+1, INSTR(NAME, ',', 1, 2)-INSTR(NAME, ',')-1)FIRST

NAME

String: Full string to read
INSTR(NAME, ‘,’)+1

Substring: Start position of SUBSTR read. Use the position of the first occurrence of comma in the name string plus 1.
INSTR(NAME, ‘,’, 1, 2)-INSTR(NAME, ‘,’)-1

Substring characters: Number of characters to read for the SUBST value. Use position of second occurrence of comma in the name string minus the first occurrence of comma. The last -1 is used to prevent a comma from being included in the SUBST value.

LENGTH Function

Used to determine the number of characters in a string

SELECT DISTINCT LENGTH(ADDRESS)
  FROM CUSTOMERS
  ORDER BY LENGTH(ADDRESS) DESC;

LPAD and RPAD Functions

Used to pad, or fill in, a character string to a fixed width

SELECT FIRSTNAME, LPAD(FIRSTNAME, 12, ' '), LPAD(FIRSTNAME, 12, '*')
  FROM CUSTOMERS
  WHERE FIRSTNAME LIKE 'J%';

LTRIM and RTRIM Functions

Used to remove a specific string of characters

SELECT LASTNAME, ADDRESS, LTRIM(ADDRESS, 'P.O BOX')
  FROM CUSTOMERS
  WHERE STATE = 'FL';

REPLACE Function

Substitutes a string with another specified string

SELECT ADDRESS, REPLACE(ADDRESS, 'P.O.', 'POST OFFICE')
  FROM CUSTOMERS
  WHERE STATE = 'FL';

TRANSLATE Function

SELECT NAME, TRANSLATE(NAME, ',', '-'), TRANSLATE(NAME, ',A', '-a')
  FROM CONTACTS;

CONCAT Function

Used to concatenate two character strings

SELECT FIRSTNAME, LASTNAME, CONCAT('CUSTOMER NUMBER: ', CUSTOMER#) "NUMBER"
  FROM CUSTOMERS
  WHERE STATE = 'FL';

Numeric Functions

Allow for manipulation of numeric data

ROUND
TRUNC
MOD
ABS

ROUND Function

Used to round numeric columns to a stated precision

SELECT TITLE, RETAIL, ROUND(RETAIL, 1), ROUND(RETAIL, 0), ROUND(RETAIL, -1)
  FROM BOOKS;

TRUNC Function

Used to truncate a numeric value to a specific position

SELECT TITLE, RETAIL, TRUNC(RETAIL, 1), TRUNC(RETAIL, 0), TRUNC(RETAIL, -1)
  FROM BOOKS;

MOD Function

SELECT TITLE, RETAIL, MOD(RETAIL, 1) AS REMAINDER_1_DECIMAL, MOD(RETAIL, 10) AS REMAINDER_TENS_PLACE
  FROM BOOKS;

ABS Function

SELECT PUBDATE, SYSDATE, ROUND(PUBDATE-SYSDATE) "DAYS", ABS(ROUND(PUBDATE-SYSDATE)) "ABS DAYS"
  FROM BOOKS
  WHERE CATEGORY = 'CHILDREN';

Date Functions

Used to perform date calculations or format date values
Subtract date for number of days difference

SELECT ORDER#, SHIPDATE, ORDERDATE, (SHIPDATE-ORDERDATE) DELAY
  FROM ORDERS
  WHERE ORDER# = 1004;

MONTHS_BETWEEN Function

Determines the number of months between two dates

SELECT TITLE, MONTHS_BETWEEN(ORDERDATE, PUBDATE) MTHS
  FROM BOOKS JOIN ORDERITEMS USING (ISBN) JOIN ORDERS USING (ORDER#)
  WHERE ORDER# = 1004;

ADD_MONTHS Functions

Adds a specified number of months to a date

SELECT TITLE, PUBDATE, ADD_MONTHS('01-DEC-08', 18) "RENEGOTIATE DATE", ADD_MONTHS(PUBDATE, 84) "DROP DATE"
  FROM BOOKS
  WHERE CATEGORY = 'COMPUTER'
  ORDER BY "RENEGOTIATE DATE";

NEXT_DAY Functions

Determines the next occurrence of a specified day of the week after a given date

SELECT ORDER#, ORDERDATE, NEXT_DAY(ORDERDATE, 'MONDAY')
  FROM ORDERS
  WHERE ORDER# = 1018;

TO_DATE Function

Converts various date formats to the internal format (DD-MON-YY) used by Oracle 11g

SELECT ORDER#, ORDERDATE, SHIPDATE
  FROM ORDERS
  WHERE ORDERDATE = TO_DATE('MARCH 31, 2009', 'MONTH DD, YYYY');

Format Model Elements - Dates

ELEMENT	DESCRIPTION	EXAMPLE
MONTH	Name of the month spelled out and padded with blank spaces to a total width of nine characters	APRIL
MON	Three-letter abbreviation for the name of the month	APR
MM	Two-digit numeric value for the month	04
RM	Roman numeral representing the month	IV
D	Numeric value for the day of the week	Wednesday = 4
DD	Numeric value for the day of the month	28
DDD	Numeric value for the day of the year	December 31 = 365
DAY	Name of the day of the week, padded with blank spaces to a length of nine characters	WEDNESDAY
DY	Three-letter abbreviation for the day of the week	WED
YYYY	Displays the four-digit numeric value of the year	2009
YYY or YY or Y	The last three, two, or single digits of the year	2009 = 009; 2009 = 09; 2009 = 9
YEAR	Spelled-out version of the year	TWO THOUSAND NINE
B.C. or A.D.	Value indicating B.C. or A.D.	2009 A.D.

ROUND Function

SELECT PUBDATE, ROUND(PUBDATE, 'MONTH'), ROUND(PUBDATE, 'YEAR')
  FROM BOOKS;

TRUNC Function

SELECT TITLE, TRUNC(MONTHS_BETWEEN(ORDERDATE, PUBDATE), 0) MTHS
  FROM BOOKS JOIN ORDERITEMS USING (ISBN) JOIN ORDERS USING (ORDER#)
  WHERE ORDER# = 1004;

Regular Expressions

Regular expressions allow the description of complex patterns in textual data

REGEXP_LIKE

SELECT *
  FROM SUPPLIERS;

Other Expressions

NVL
NVL2
TO_CHAR
DECODE
SOUNDEX

NVL Function

Substitutes a value for a NULL value

SELECT ORDER#, ORDERDATE, SHIPDATE, NVL(SHIPDATE, '06-APR-09')-ORDERDATE "SHIP DAYS"
  FROM ORDERS
  WHERE ORDERDATE >= '03-APR-09';

NVL2 Functions

Allows different actions based on whether a value is NULL

SELECT ORDER#, ORDERDATE, NVL2(SHIPDATE, 'SHIPPED', 'NOT SHIPPED') "STATUS"
  FROM ORDERS
  WHERE ORDERDATE >= '03-APR-09';

NULLIF Function

SELECT O.CUSTOMER#, ORDER#, ISBN, OI.PAIDEACH, B.RETAIL, NULLIF(OI.PAIDEACH, B.RETAIL)
  FROM ORDERS O JOIN ORDERITEMS OI
    USING (ORDER#)
    JOIN BOOKS B USING (ISBN)
  WHERE ORDER# IN(1001, 1007)
  ORDER BY ORDER#;

TO_CHAR

Converts dates and numbers to a formatted character string

SELECT TITLE, TO_CHAR(PUBDATE, 'MONTH DD, YYYY') "PUBLICATION DATE", TO_CHAR(RETAIL, '$999.99') "RETAIL PRICE"
  FROM BOOKS
  WHERE ISBN = 0401140733;

Format Model Elements – Time and Number

Time Elements
SS	Seconds	Value between 0–59
SSSS	Seconds past midnight	Value between 0–86399
MI	Minutes	Value between 0–59
HH or HH12	Hours	Value between 1–12
HH24	Hours	Value between 0–23
A.M. or P.M.	Value indicating morning or evening hours	A.M. (before noon) or P.M. (after noon)

Number Elements
`9`	Indicates display width with a series of 9s but doesn’t display insignificant leading zeros	99999
`0`	Displays insignificant leading zeros	00099999
`$`	Displays a floating dollar sign	$99999
`.`	Indicates the decimal position	999.99
`,`	Displays a comma in the indicated position	9,999

DECODE Function

Determines action based upon values in a list

SELECT CUSTOMER#, STATE,
        DECODE(STATE, 'CA', .08,
                      'FL', .07,
                              0) "SALES TAX RATE"
  FROM CUSTOMERS
  WHERE STATE IN('CA', 'FL', 'GA', 'TX');

CASE Expression

SELECT EMPNO, LNAME, FNAME,
  ROUND(MONTHS_BETWEEN('01-JUL-09', HIREDATE)/12,2) "YEARS",
  CASE
    WHEN (MONTHS_BETWEEN('01-JUL-09', HIREDATE)/12) < 4 THEN 'LEVEL 1'
    WHEN (MONTHS_BETWEEN('01-JUL-09', HIREDATE)/12) < 8 THEN 'LEVEL 2'
    WHEN (MONTHS_BETWEEN('01-JUL-09', HIREDATE)/12) < 11 THEN 'LEVEL 3'
    WHEN (MONTHS_BETWEEN('01-JUL-09', HIREDATE)/12) < 15 THEN 'LEVEL 4'
    ELSE 'LEVEL 5'
  END "RETIRE LEVEL"
FROM EMPLOYEES;

SOUNDEX Function

References phonetic representation of words

SELECT CUSTOMER#, LASTNAME, FIRSTNAME
  FROM CUSTOMERS
  WHERE SOUNDEX(LASTNAME) = SOUNDEX('SMYTH');

TO_NUMBER Function

SELECT TITLE, PUBDATE,
  TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - TO_NUMBER(TO_CHAR(PUBDATE, 'YYYY')) "YRS"
  FROM BOOKS
  WHERE CATEGORY = 'COMPUTER';

DUAL Table

Dummy table
Consists of one column and one row
Can be used for table reference in the FROM clause

Using DUAL

SELECT ROUND(4769.43, -2), LENGTH('HELLO')
  FROM DUAL;

Summary

Single-row functions return a result for each row or record processed
Case conversion functions such as UPPER, LOWER, and INITCAP can be used to alter the case of character strings
Character manipulation functions can be used to extract substrings (portions of a string), identify the position of a substring in a string, replace occurrences of a string with another string, determine the length of a character string, and trim spaces or characters from strings
Nesting one function within another allows multiple operations to be performed on data
Simple number functions such as ROUND and TRUNC can round or truncate a number on both the left and right side of a decimal
The MOD function is used to return the remainder of a division operation
Date functions can be used to perform calculations with dates or to change the format of dates entered by a user
Regular expressions enable complex pattern matching operations
The NVL, NVL2, and NULLIF functions are used to address problems encountered with NULL values
The TO_CHAR function lets a user present numeric data and dates in a specific format
The DECODE function allows an action to be taken to be determined by a specific value
The searched CASE expression enables you to evaluate conditions to determine the resulting value
The SOUNDEX function looks for records based on the phonetic representation of characters
The DUAL table can be helpful when testing functions

C.11 Demo

-- Week 11

-- DUAL FUNCTION

SELECT SYSDATE
    FROM DUAL;

SELECT ('CENTENNIAL')
    FROM DUAL;

DESC DUAL;

SELECT initcap('CENTENNIAL college')
    FROM DUAL;

SELECT SYSDATE
    FROM DUAL;

SELECT * FROM CUSTOMERS;

-- CASE CONVERSION

SELECT LOWER(FIRSTNAME) FNAME, LOWER(LASTNAME) LNAME
    FROM CUSTOMERS;

SELECT INITCAP(FIRSTNAME) FNAME, INITCAP(LASTNAME) LNAME
    FROM CUSTOMERS;

SELECT LOWER(FIRSTNAME) FNAME, LOWER(LASTNAME) LNAME, STATE
    FROM CUSTOMERS
    WHERE STATE=UPPER('TX');

SELECT LASTNAME,FIRSTNAME
    FROM CUSTOMERS
    WHERE lower(LASTNAME)='smith';

SELECT LASTNAME,FIRSTNAME,ADDRESS,STATE
    FROM CUSTOMERS
    WHERE STATE=UPPER('&STATE');

SELECT * FROM CUSTOMERS;

-- DISPLAY CUSTOMER WHOSE FIRSTNAME STARTS WITH 'J'

SELECT INITCAP(FIRSTNAME) FNAME, INITCAP(LASTNAME) LNAME
    FROM CUSTOMERS
    WHERE FIRSTNAME LIKE 'J%';

-- CHARACTER MANIPULATION FUNCTIONS: SUBSTRING

SELECT UNIQUE CUSTOMER#, SUBSTR(CUSTOMER#, -3, 3) "LAST 3 DIGITS"
    FROM CUSTOMERS
    WHERE SUBSTR(CUSTOMER#, -3, 3) < 011;
--> -3: This is the starting position from where the substring extraction begins.
--> 3: This is the length of the substring to be extracted.
-- | 9 | 8 | 1 | 1 | 5 |
-- |   |   | -3| -2|-1 | -> SQL, PYTHON 에서 모두 마지막 인덱스는 -1이다.

SELECT ADDRESS,SUBSTR(ADDRESS,10)
    FROM CUSTOMERS
    WHERE ADDRESS LIKE 'P.O. BOX%';

SELECT ZIP,SUBSTR(ZIP,2,4)
    FROM CUSTOMERS;

SELECT ZIP,SUBSTR(ZIP,4)
    FROM CUSTOMERS;

-- CHARACTER MANIPULATION FUNCTIONS: INSTR



-- CHARACTER MANIPULATION FUNCTIONS: LENGTH

SELECT FIRSTNAME,LENGTH(FIRSTNAME)
    FROM CUSTOMERS
    ORDER BY LENGTH(FIRSTNAME) DESC;

--> CHECK YOUR NUMBER OF CHAR IN YOUR FIRSTNAME AND LASTNAME

SELECT LENGTH('VIJI')
    FROM DUAL;

SELECT * FROM CUSTOMERS;

-- CHARACTER MANIPULATION FUNCTIONS: LPAD & RPAD



-- CHARACTER MANIPULATION FUNCTIONS: LTRIM & RTRIM

--> LTRIM AND RTRIM IN CUSTOMERS TABLE

SELECT CITY, STATE, ADDRESS
    FROM CUSTOMERS
    WHERE CITY = 'SEATTLE';

SELECT ADDRESS
    FROM CUSTOMERS
    WHERE ADDRESS LIKE 'P%';

SELECT ADDRESS, LTRIM(ADDRESS, 'P.O. BOX')
    AS ADDRESS_TRIMMED
    FROM CUSTOMERS
    WHERE ADDRESS LIKE 'P%';

SELECT ADDRESS, LTRIM(ADDRESS,'P.O. BOX')
    AS PBNUMBER
    FROM CUSTOMERS;

SELECT CITY FROM CUSTOMERS;

SELECT LTRIM(ADDRESS) || ' ' || RTRIM(CITY,'-')
    FROM CUSTOMERS;

-- CHARACTER MANIPULATION FUNCTIONS: REPLACE



-- CHARACTER MANIPULATION FUNCTIONS: TRANSLATE

--SELECT TRANSLATE("SQL*Plus User"s Guide','*/",'___''CENTENNIAL'S POLICIES ARE DIFFERENT') FROM DUAL;

-- CHARACTER MANIPULATION FUNCTIONS: CONCAT

SELECT CONCAT(CONCAT(LASTNAME, '''s CITY IS  '), CITY)
    FROM CUSTOMERS;

SELECT CONCAT(CONCAT(firstname, ' ID: '),customer#)
    FROM customers;

SELECT * FROM BOOKS;

SELECT LASTNAME, RTRIM(LASTNAME, 'S')
    FROM CUSTOMERS;

---------------------------------------------------------------------------------------------------------

-- CLASS ACTIVITY (10 MARKS)

-- 1. CREATE 10 QUERIES USING THE SINGLE ROW FUNCTIONS
-- 2. YOU CAN USE DUAL AND TABLES IN JLDB DATABASE

-- CASE CONVERSION
SELECT * FROM ORDERS;
SELECT LOWER(SHIPSTREET) SHIPSTREET, LOWER(SHIPCITY) SHIPCITY
    FROM ORDERS;

SELECT INITCAP('SEYEON JO')
    FROM DUAL;

-- SUBSTR
SELECT SHIPZIP, SUBSTR(SHIPZIP,3,4)
    AS SHIPZIP_TRIMMED
    FROM ORDERS;

-- LENGTH
SELECT * FROM AUTHOR;
SELECT FNAME,LENGTH(FNAME)
    AS FNAME_LENGTH
    FROM AUTHOR
    ORDER BY LENGTH(FNAME) DESC;

-- LTRIM
SELECT * FROM ORDERITEMS;
SELECT ORDER#, LTRIM(ORDER#, '1')
    AS ORDER#_TRIMMED
    FROM ORDERITEMS;

---------------------------------------------------------------------------------------------------------

-- WK12-2

-- CHALLENGE 1

-- JOIN CUSTOMERS,ORDERS,ORDERITEM ,BOOKS . WHICH CUSTOMER
--HAS ORDERED WHAT BOOK.
--O/P   BONITAS ORDERED THE BOOK BODYBUILD IN 1O
SELECT c.firstname || ' ' || c.lastname AS name, b.title
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn);

SELECT CONCAT(CONCAT(FIRSTNAME,' ORDERED '),TITLE) AS "CUSTOMER ORDER"  FROM CUSTOMERS
 JOIN ORDERS USING(CUSTOMER#)
 JOIN ORDERITEMS USING (ORDER#)
 JOIN BOOKS USING(ISBN);


SELECT CONCAT(CONCAT(CONCAT(CONCAT(c.lastname,'-'),C.FIRSTNAME),' ORDER THE BOOK '), b.title)
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn);

SELECT * FROM ORDERS;
SELECT * FROM ORDERITEMS;

-- CHALLEGE 2 . HOW MANY DAYS ARE BETWEEN ORDERDATE AND SHIPDATE

SELECT CONCAT(CONCAT(CONCAT(CONCAT(c.lastname,'-'),C.FIRSTNAME),' ORDER THE BOOK '), b.title),
(o.shipdate-o.orderdate) as days,o.SHIPDATE
FROM customers c
JOIN orders o USING(customer#)
JOIN orderitems io USING(order#)
JOIN books b USING(isbn)
where o.shipdate is not null;


SELECT order#,CONCAT(shipdate - orderdate,'DAY(S)') "DTIME" FROM
ORDERS ;

SELECT CONCAT(CONCAT(CONCAT(SHIPDATE, ' SHIPPED '), ORDERDATE), SHIPDATE - ORDERDATE)  FROM ORDERS
INNER JOIN orderitems USING (ORDER#);


SELECT CONCAT(CONCAT(CONCAT(CONCAT('ORDER #', ORDER#), ' TOOK '),(SHIPDATE-ORDERDATE)), ' DAYS TO SHIP.')
AS DTIME
FROM ORDERS
WHERE SHIPDATE IS NOT NULL;

SELECT customer#,firstname || '''s order ' ||
DECODE(shipdate - orderdate, 0, 'shipped immediately!', 'took ' || (shipdate - orderdate) || ' day(s) to ship.') "Order info"
FROM customers
INNER JOIN orders USING (customer#)
WHERE shipdate IS NOT NULL
ORDER BY (shipdate - orderdate);

Seyeon

Terminology

Types of Functions

Case Conversion Functions

LOWER Function

UPPER Function

INITCAP Function

Character Manipulation Functions

SUBSTR Function

INSTR Function

Nesting Function

LENGTH Function

LPAD and RPAD Functions

LTRIM and RTRIM Functions

REPLACE Function

TRANSLATE Function

CONCAT Function

Numeric Functions

ROUND Function

TRUNC Function

MOD Function

ABS Function

Date Functions

MONTHS_BETWEEN Function

ADD_MONTHS Functions

NEXT_DAY Functions

TO_DATE Function

Format Model Elements - Dates

ROUND Function

TRUNC Function

Regular Expressions

REGEXP_LIKE

Other Expressions

NVL Function

NVL2 Functions

NULLIF Function

TO_CHAR

Format Model Elements – Time and Number

DECODE Function

CASE Expression

SOUNDEX Function

TO_NUMBER Function

DUAL Table

Using DUAL

Summary

C.11 Demo

See other articles in Category Oracle-Sql

Leave a comment

Lastest article 10 :)