5. Using Single-Row Functions to Customize Output

Functions: What are the three characteristics of SQL functions?

May take parameters, perform processing with the parameter data, always return one single answer.

Which types of functions are in SQL?

built-in and user-defined

What are built-in functions in SQL?

Are part of the SQL language. They are in every standard implementation of SQL.

Functions: Where can built‑in functions be invoked?

Anywhere an expression is allowed: SELECT list and WHERE, INSERT VALUES, UPDATE SET/WHERE, DELETE WHERE, and more.

What kind of built-in functions are there? Categories.

Character, Number and Date functions.

What does DATE subtraction return?

It returns difference, number of days

DUAL: What is the DUAL table in Oracle?

A one-row, one-column table (DUMMY VARCHAR2(1) with value 'X') used to select expressions or function results.

DUAL: Why select from DUAL?

To evaluate functions or expressions without referencing application tables, e.g., SELECT SYSDATE FROM DUAL. It returns only one row because the dual table has one row.

UPPER: What does UPPER(s1) do?

Converts s1 to uppercase; returns a character string.

s1 is a required character string

UPPER: When searching case-insensitively, how should UPPER be applied?

Standardize both sides: WHERE UPPER(col) = UPPER(:bind) to avoid case mismatch.

LOWER: What does LOWER(s1) do?

Converts s1 to lowercase; returns a character string.

s1 is a required character string

LOWER: Give a safe equality predicate using LOWER for mixed case columns.

WHERE LOWER(last_name) = LOWER(family_name).

INITCAP: What does INITCAP(s1) do?

Capitalizes the first letter of each word and lowercases the rest; returns a character string.

s1 is a required character string

INITCAP: Why can INITCAP produce undesirable results for names like 'McDonald’s'?

It treats punctuation as word separators and lowercases internal letters, e.g., 'McDonald''s' -> 'Mcdonald''S'.

String quotes: How do you include a single quote character in a string literal?

Escape by doubling it: 'O''Brien' or 'McDonald''s'.

String quotes: Why is escaping needed for names like O’Hearn?

Because single quote is the string delimiter; doubling it tells SQL to treat it as data, not a terminator.

CONCAT / ||: What does CONCAT(s1,s2) do and what is its limitation?

Concatenates only two strings into one string; accepts exactly two parameters.

s1 and s2 are both character strings and both are required

CONCAT / ||: How to concatenate more than two strings?

Use the || operator repeatedly, e.g., s1 || s2 || s3.

the result is a single string

LPAD/RPAD: What do LPAD(s1,n,s2) and RPAD(s1,n,s2) do?

Pad s1 on the left/right with s2 so the result length is n.

s1 - character string - required

n - number - required

s2 - character string - optional, defaults to a single blank space if omitted

LPAD/RPAD: Give a formatting example with LPAD/RPAD.

LPAD(‘Ahoj’, 10, ‘.’) => ……Ahoj

LTRIM/RTRIM: What do LTRIM(s1,s2) and RTRIM(s1,s2) do?

Remove occurrences of characters in s2 from the left or right of s1.

s1, s2 - both are character strings

s1 - required

s2 - optional, if omitted, the default is a single blank space

LTRIM/RTRIM: How to strip leading dashes from '---abc'?

SELECT LTRIM('---abc','-') FROM DUAL -> 'abc'.

TRIM: What is the syntax and defaults for TRIM?

TRIM(trim_info trim_char FROM trim_source);

• trim_info - one of [LEADING|TRAILING|BOTH], defaults to BOTH if omitted

• trim_char - single char to be trimmed - defaults to blank space if omitted

• trim_source - string, defaults to return NULL if omitted

• if either trim_source or trim_character is null then result is null

• returns varchar2

Works:

• TRIM(DATE ‘2025-02-02’) → 02-FEB-25

• TRIM(2) → 2

• TRIM(1 FROM 123) → 23

• TRIM(null) → null

• TRIM(‘ ‘) → null

• TRIM(‘0’ FROM DATE’2025-02-02’) → 2-FEB-25

Doesn’t work:

• TRIM(FROM)

• TRIM(FROM ‘abc’)

• TRIM(‘abe’ FROM ‘abc’)

• TRIM(‘a’ FROM)

TRIM: How to remove trailing dashes from 'Seven thousand--------'?

SELECT TRIM(TRAILING '-' FROM 'Seven thousand--------') FROM DUAL -> 'Seven thousand'.

LENGTH: What does LENGTH(s) return and what is the type?

Returns the length of s as a number.

s - string, required

LENGTH: Example of LENGTH with a long word.

SELECT LENGTH('antidisestablishmentarianism') FROM DUAL -> 28

INSTR: What does INSTR(s1, s2, pos, n) do?

Returns the position of the nth occurrence of s2 in s1 starting at pos

• s1 - source string, required

• s2 - substring, required

• pos - starting position in s1, optional, default 1, if negative the search in s1 for occurrences of s2 starts at the end of the string and moves backward

• n - nth occurrence of s2 to locate, optional, default 1

What’s the result of this query?

select INSTR('Hellow World, Bordie', 'or', -13, 1)

from dual;

0

What’s the result of this query?

select INSTR('Hellow World, Bordie', 'or', -12, 1)

from dual;

9

What’s the result of this query?

select INSTR('Hellow World, Bordie', 'or', -5, 1)

from dual;

16

SUBSTR: What does SUBSTR(s, pos, len) do?

Returns substring of s starting at pos for len chars; if len omitted, to end; negative pos counts from end.

• s - string, required

• pos - number, required

• len - number, optional

SUBSTR: Extract the name from 'Name: MARK KENNEDY' starting at 7.

SELECT SUBSTR('Name: MARK KENNEDY',7) FROM DUAL -> 'MARK KENNEDY'.

What does this query return?

select SUBSTR('Hellow World, Bordie', -2, 2)

from dual;

ie

SOUNDEX: What does SOUNDEX(s) return and why use it?

A 4-character phonetic code to match similar-sounding words. It translates English word (a source string) into its SOUNDEX code. Useful for sounding alike sounds tend to generate the same soundex pattern.

• s - source string, required

How does SOUNDEX work?

• First letter remains same

• Next letters are translated into a numeric code according to the rules until three digits are generated. If any letters exist beyond that, they are ignored. The result is pad with 0 until it’s a 4 char code.

Which letters get translated into soundex code 1?

B, F, P, V

Which letters get translated into soundex code 2?

C, G, J, K, Q, S, X, Z

Which letters get translated into soundex code 3?

D, T

Which letters get translated into soundex code 4?

L

Which letters get translated into soundex code 5?

M, N

Which letters get translated into soundex code 6?

R

Which letters are ignored when translating them into soundex code?

A, E, H, I, O, U, W, Y

What is the soundex code for “dog”?

D200

SOUNDEX: Why apply SOUNDEX on both sides of a comparison?

To compare codes, e.g., WHERE SOUNDEX(lastname)=SOUNDEX('Franklin'); using it on one side compares text to a code.

What is the SOUNDEX code for: “0“, null, ‘-1’, ‘‘, ‘-’

null

What is the SOUNDEX code for: DATE’2025-01-01’

J500 - because it gets converted to 2025-JAN-01, so JAN is translated to J500

ABS: What does ABS(n) do?

Returns the absolute value of n.

n - is required, numeric

CEIL: What does CEIL(n) return?

Smallest integer ≥ n.

• n is required, numeric

CEIL: Example: CEIL(10.01), CEIL(-2.1), CEIL(-2.9).

11, -2, and -2.

FLOOR: What does FLOOR(n) return?

Largest integer ≤ n.

n - numeric, required

FLOOR: Example: FLOOR(10.99), FLOOR(-2.1), FLOOR(-2.9).

10,-3, and -3.

ROUND (number): What is the behavior of ROUND(n,i)?

Rounds n to i decimal places (i optional, default 0); i<0 rounds to left of decimal; .5 rounds away from zero.

• n - required, numeric

• i - integer, optional, if omitted then 0

ROUND (number): Example: ROUND(12.355143,2) and ROUND(259.99,-1), ROUND(9.23, -1), ROUND(9.23, 0), ROUND(9.23, -2)

12.36, 260, 10, 9, 0

TRUNC (number): What is the behavior of TRUNC(n,i)?

Truncates n toward zero at i decimal places (i optional, default 0).

• n - required, number, can be decimal

• i - integer, optional, if omitted then 0

TRUNC (number): Example: TRUNC(12.355143,2) and TRUNC(259.99,-1), TRUNC(815623.23, -5), TRUNC(815623.23, 0)

12.35, 250, 800000, 815623

REMAINDER: What does REMAINDER(n1, n2) compute?

Returns n1 minus the nearest multiple of n2; result can be negative if the nearest multiple is above n1.

• n1, n2 - both numeric and required

REMAINDER: Example: REMAINDER(9,3), REMAINDER(10,3), REMAINDER(11,3), REMAINDER(5, -2), REMAINDER(5, -5), REMAINDER(-5, -2), REMAINDER(-5, 2)

0, 1, -1, 1, 0, -1, -1

MOD: what’s the syntax of MOD function?

MOD(n1,n2): n1, n2 are both numeric and required

Returns n1 minus the nearest multiple of n2 but that the difference is a plus number

MOD: How does MOD(n1,n2) differ from REMAINDER?

MOD uses FLOOR instead of ROUND; returns a nonnegative remainder for positive n2.

MOD: Example comparison for (5,3).

MOD(5,3)=2; REMAINDER(5,3)=-1.

SIGN: What does SIGN(n) return?

n is numeric and required

1 if n>0, 0 if n=0, -1 if n<0.

SIGN: Use SIGN to test positivity without CASE.

WHERE SIGN(n)=1 filters positive values.

SQRT: What does SQRT(n) return?

n is numeric and required. The square root of n.

SQRT: Give a simple example of SQRT.

SELECT SQRT(25) FROM DUAL -> 5.

Define function POWER

POWER(n2, n1) - n2 raised to the n1 power.

n1 and n2 are both numeric and required.

if n2 is negative then n1 must be integer

What does function USER do?

Returns the session user (schema)

What does function UID do?

Returns a unique integer that identifies the session user

What does CURRENT_DATE function do?

Returns the current date in the session time zone. Date data type.

What does CURRENT_TIMESTAMP function do?

Returns the current date and time in the session time zone. Timestamp with time zone data type.

What does DBTIMEZONE function do?

Returns the value of the database time zone. Return type is a time zone offset. Example result: +00:00 (but this is based on the database settings of specifying the db time zone display settings)

What does EXTRACT function do?

Extracts the returns the value of a specified datetime field from a datetime or interval expression.

EXTRACT(datetime_field FROM expr)

• datetime_field is required and one of these: YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|TIMEZONE_HOUR|TIMEZONE_MINUTE|TIMEZONE_REGION|TIMEZONE_ABBR

• FROM and expr is also required

expr must be:

• for YEAR or MONTH => DATE, TIMESTAMP, TS WITH TZ, TS WITH LOCAL TZ or INTERVAL YEAR TO MONTH

• for DAY => DATE, TIMESTAMP, TS W TZ, TS W L TZ, INTERVAL DAY TO SECOND

• for HOUR, MINUTE, or SECOND, then expr must be evaluate to timestamp, TS with TZ, TS with local TZ, or interval day to second. Date is not valid here.

• for TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET then expr must evaluate to TS WITH TZ or TS WITH LOCAL TZ

SYSDATE: What does SYSDATE return and where is it sourced?

Current date and time from the database server OS; time exists but may not display per NLS_DATE_FORMAT.

No parameters.

SYSDATE: How to display the time portion of SYSDATE?

Use TO_CHAR with a format model, e.g., TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS').

ROUND (d, i): What does do for dates?

Rounds d to the nearest unit per i; default rounds to nearest whole day; 12:00 rounds up to next day.

d - required date

i - format model, optional

ROUND (date, i): Example showing bias at noon.

ROUND(TO_DATE('2020-08-01 12:00','YYYY-MM-DD HH24:MI'), 'DD') -> next day (2020‑08‑02).

TRUNC (date, fmt): What does it do for dates?

Always rounds down to the boundary of fmt; default is day (time set to 00:00).

• d is a date, required

• fmt is a format model, optional

TRUNC (date, i): Example of month truncation.

TRUNC(DATE '2020-08-18','MM') -> 2020‑08‑01 00:00.

NEXT_DAY: What does NEXT_DAY(d,c) return?

a date of the first occurrence of weekday c strictly after date d; c is a text day name per session NLS settings.

• d is date, required

• c is a text reference to a day of the week, required

NEXT_DAY: Example: next Saturday after 2019-05-31.

NEXT_DAY(DATE '2019-05-31','SATURDAY') -> 2019‑06‑01.

LAST_DAY: What does LAST_DAY(d) return?

The last day of the month that contains d.

• d is required, date

LAST_DAY: Example for leap and common years.

LAST_DAY(DATE '2020-02-10')=2020‑02‑29; LAST_DAY(DATE '2021-02-10')=2021‑02‑28.

ADD_MONTHS: What does ADD_MONTHS(d,n) do?

Returns a date that adds n months to d (n can be negative to subtract).

• d is a date, required

• n is a whole number, required

ADD_MONTHS: Example with end-of-month behavior.

ADD_MONTHS(DATE '2017-01-31',1) -> 2017‑02‑28 (adjusts to valid date).

MONTHS_BETWEEN: What does MONTHS_BETWEEN(d1,d2) return?

Number of months between d1 and d2, possibly fractional; positive if d1>d2 else negative.

• d1 and d2 are dates, required

MONTHS_BETWEEN: Example with sign and whole months.

MONTHS_BETWEEN(DATE '2014-06-12', DATE '2013-10-03') -> positive value; reversing args yields negative.

NUMTOYMINTERVAL: What does NUMTOYMINTERVAL(n,interval_unit) return?

date type of INTERVAL YEAR TO MONTH representing n 'YEAR' or 'MONTH'.

• n is number, required

• interval_unit is YEAR or MONTH

NUMTOYMINTERVAL: Example of 27 months.

NUMTOYMINTERVAL(27,'MONTH') -> +02-03 → (2 years, 3 months).

NUMTODSINTERVAL: What does NUMTODSINTERVAL(n,interval_unit) return?

A value of the data type INTERVAL DAY TO SECOND representing n 'DAY'|'HOUR'|'MINUTE'|'SECOND'.

• n is number, required

• interval_unit = one of DAY, HOUR, MINUTE, or SECOND

NUMTODSINTERVAL: Example of 36 hours.

NUMTODSINTERVAL(36,'HOUR') -> +01 12:00:00.000000.

DATE arithmetic: What numeric literal equals one day in DATE arithmetic?

1 equals one day; 1/24 equals one hour; 1/1440 equals one minute.

DATE arithmetic: Add minutes to a DATE without functions. How to add one day to SYSDATE?

SYSDATE + (12/1440) → adds 12 minutes or SYSTIMESTAMP + (12/1440) → adds 12 minutes

SYSDATE + 1 → adds 1 day, or SYSTIMESTAMP + 1 → adds 1 day

What can analytical functions do?

Analytical functions in Oracle SQL compute values across a set of rows that are related to the current row, without collapsing the rows into a single result as aggregate functions do. They operate over a "window" of rows defined by a clause (like OVER with PARTITION BY and ORDER BY), and return a value for each row in that window. This allows row-by-row analysis with context from surrounding rows, such as rankings, running totals, or moving averages

Analytic placement: Where can analytic functions appear in a query?

SELECT list or ORDER BY only; not allowed in WHERE, GROUP BY, or HAVING.

Analytic order: When are analytic functions evaluated?

As the last step before ORDER BY of the query.

OVER: What does the OVER clause do?

Defines the analytic window and ordering for a function to operate over rows.

OVER: How does OVER enable running totals?

SUM(col) OVER(ORDER BY key ROWS UNBOUNDED PRECEDING) computes a cumulative total per order.

PARTITION BY: What does PARTITION BY specify in analytics?

Divides rows into groups; analytics reset per partition.

PARTITION BY: Effect of PARTITION BY on running totals?

Running totals restart at the first row of each partition.

ORDER BY in OVER: Is ORDER BY in OVER tied to the query ORDER BY?

No; it defines analytic computation order independent of the SELECT's ORDER BY.

ORDER BY in OVER: What happens if SELECT ORDER BY changes but OVER stays the same?

Computed analytic values per row remain the same; only output order changes.