Oracle SQL Single-Row Functions: Character, Number, Date and Conversion

Oracle SQL Single-Row Functions: Complete Guide

Single-row functions are the most frequently used tools in Oracle SQL. They allow you to transform, format, and manipulate individual values — whether text, numbers, or dates — directly inside SELECT, WHERE, ORDER BY, and other clauses.

Learning Objectives
By the end of this guide you will master the most important character, number, date, and conversion functions, understand argument order and edge cases, and know how to combine them effectively in real queries.

1. Character Functions — Case and Padding

-- Case conversion
UPPER('hello')          -- Returns: 'HELLO'
LOWER('HELLO')          -- Returns: 'hello'
INITCAP('hello world')  -- Returns: 'Hello World'

-- Padding
LPAD('SQL', 7, '*')     -- Returns: '****SQL'
RPAD('SQL', 7, '*')     -- Returns: 'SQL****'
LPAD('SQL', 7)          -- Returns: '    SQL' (default pad is space)

-- Trimming
TRIM(' hello ')         -- Returns: 'hello'
TRIM('H' FROM 'Hello')  -- Returns: 'ello'
LTRIM('***SQL***', '*') -- Returns: 'SQL***'
RTRIM('***SQL***', '*') -- Returns: '***SQL'

LPAD / RPAD tip: The second argument is the **final total length**, not the number of padding characters. If the original string is already longer, it gets truncated.

2. Character Functions — Searching and Extracting

-- Length
LENGTH('Hello')         -- Returns: 5
LENGTH(NULL)            -- Returns: NULL

-- SUBSTR (1-based indexing)
SUBSTR('ABCDEFG', 3, 4) -- Returns: 'CDEF'
SUBSTR('ABCDEFG', 3)    -- Returns: 'CDEFG'
SUBSTR('ABCDEFG', -3)   -- Returns: 'EFG' (negative = from end)

-- INSTR (returns position, or 0 if not found)
INSTR('HELLO WORLD', 'O')        -- Returns: 5
INSTR('HELLO WORLD', 'O', 1, 2)  -- Returns: 8 (second occurrence)

-- Other useful functions
REPLACE('JACK AND JUE', 'J', 'BL')  -- Returns: 'BLACK AND BLUE'
CONCAT('Hello', ' World')           -- Returns: 'Hello World'

INSTR behavior: Returns 0 when the substring is not found (never NULL unless the input string is NULL).

3. Number Functions — ROUND, TRUNC, MOD

-- ROUND (rounds to nearest value)
ROUND(45.926, 2)   -- Returns: 45.93
ROUND(45.926, 0)   -- Returns: 46
ROUND(45.926, -1)  -- Returns: 50   (nearest 10)

-- TRUNC (drops digits without rounding)
TRUNC(45.926, 2)   -- Returns: 45.92
TRUNC(45.926, -1)  -- Returns: 40   (nearest 10)

-- MOD (remainder)
MOD(10, 3)         -- Returns: 1

Negative decimal places: Both ROUND and TRUNC accept negative numbers to work on the left side of the decimal point.

4. Date Functions — Core Operations

-- Current date and time
SYSDATE

-- Add or subtract months
ADD_MONTHS(SYSDATE, 6)          -- 6 months from today
ADD_MONTHS(SYSDATE, -3)         -- 3 months ago

-- Months between two dates
MONTHS_BETWEEN(SYSDATE, hire_date)   -- positive result when SYSDATE is later

-- Next and last day
NEXT_DAY(SYSDATE, 'FRIDAY')     -- Next Friday
LAST_DAY(SYSDATE)               -- Last day of current month

MONTHS_BETWEEN order: Later date first → positive result.

5. Date Arithmetic

SYSDATE + 7          -- 7 days from today
SYSDATE - 30         -- 30 days ago
SYSDATE + 1/24       -- 1 hour from now
SYSDATE - hire_date  -- Returns number of days (can be fractional)

6. Conversion Functions — TO_CHAR, TO_NUMBER, TO_DATE

-- TO_CHAR (value → formatted string)
TO_CHAR(SYSDATE, 'DD-MON-YYYY')           -- '27-MAR-2026'
TO_CHAR(1234567.89, '$9,999,999.99')      -- '$1,234,567.89'

-- TO_DATE (string → date)
TO_DATE('27-03-2026', 'DD-MM-YYYY')

-- TO_NUMBER (string → number)
TO_NUMBER('1,234.56', '9,999.99')

Argument order is always: value first, format model second.

7. Format Models (Common Elements)

Element	Meaning	Example
YYYY	4-digit year	2026
MM	Month number	03
MON	Month abbreviation	MAR
DD	Day of month	27
HH24	24-hour format	14
MI	Minutes	35
$ , .	Currency & separators	$1,234.56

8. Conditional Expressions — CASE and DECODE

-- Simple CASE
CASE job_id
  WHEN 'IT_PROG' THEN 'Developer'
  WHEN 'SA_REP'  THEN 'Sales Rep'
  ELSE 'Other'
END

-- Searched CASE
CASE
  WHEN salary < 5000  THEN 'Low'
  WHEN salary < 10000 THEN 'Mid'
  ELSE 'High'
END

-- DECODE (Oracle-specific)
DECODE(job_id, 'IT_PROG', 'Developer', 'SA_REP', 'Sales Rep', 'Other')

9. Interactive Practice Quiz

Q1. What does this return?
SUBSTR('ORACLE DATABASE', 8, 4)

A. 'ORAC' B. 'DATA' C. 'ACLE' D. 'BASE'

Q2. Which expression correctly calculates months worked since hire_date?

A. MONTHS_BETWEEN(hire_date, SYSDATE) B. MONTHS_BETWEEN(SYSDATE, hire_date) C. ADD_MONTHS(hire_date, SYSDATE) D. SYSDATE - hire_date / 30

Q3. What does ROUND(156.748, -2) return?

A. 156.75 B. 156.70 C. 157 D. 200

Conclusion

Single-row functions are the building blocks of almost every useful Oracle SQL query. Mastering argument order, format models, NULL handling, and the differences between functions like ROUND vs TRUNC will dramatically improve the quality and readability of your code.

Practice combining these functions in SELECT lists and WHERE clauses — they are used everywhere in real-world Oracle development.