Single-Row Functions

8.1 Character Functions

SELECT UPPER('oracle'), LOWER('ORACLE'), INITCAP('oracle database'),
       LENGTH('Oracle'), LENGTHB('Oracle'),
       SUBSTR('Oracle Database', 8, 8), SUBSTR('Oracle', -3),
       INSTR('Oracle Database', 'a', 1, 2),
       CONCAT('Ora', 'cle'), 'Ora' || 'cle',
       LPAD('42', 5, '0'), RPAD('ab', 5, '*'),
       TRIM('  x  '), LTRIM('xxab', 'x'), RTRIM('abyy', 'y'), TRIM(BOTH '0' FROM '00120'),
       REPLACE('a-b-c', '-', '/'), TRANSLATE('abc', 'ab', 'xy'),
       REVERSE('abc'), ASCII('A'), CHR(65), SOUNDEX('Smith'),
       NLS_UPPER('straße', 'NLS_SORT=XGERMAN')
FROM dual;

8.2 Regular Expression Functions

SELECT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') FROM customers;  -- in WHERE
SELECT REGEXP_REPLACE('Phone: 98-765-43210', '[^0-9]', '') FROM dual;
SELECT REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 2) FROM dual;          -- 'b'
SELECT REGEXP_INSTR('hello world', 'o', 1, 2) FROM dual;
SELECT REGEXP_COUNT('banana', 'a') FROM dual;
-- Split a string into rows
SELECT REGEXP_SUBSTR('a,b,c', '[^,]+', 1, LEVEL) FROM dual CONNECT BY REGEXP_SUBSTR('a,b,c', '[^,]+', 1, LEVEL) IS NOT NULL;

8.3 Numeric Functions

SELECT ROUND(123.456, 2), ROUND(123.456, -1), TRUNC(123.456, 1), CEIL(4.1), FLOOR(4.9),
       MOD(10, 3), REMAINDER(10, 3), ABS(-5), SIGN(-3), POWER(2, 10), SQRT(16),
       EXP(1), LN(10), LOG(10, 100), SIN(0), WIDTH_BUCKET(42, 0, 100, 10),
       GREATEST(3, 7, 5), LEAST(3, 7, 5), BITAND(6, 3)
FROM dual;

8.4 Date Functions

SELECT SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP,
       ADD_MONTHS(SYSDATE, 3), MONTHS_BETWEEN(DATE '2025-12-31', DATE '2025-01-01'),
       NEXT_DAY(SYSDATE, 'MONDAY'), LAST_DAY(SYSDATE),
       TRUNC(SYSDATE), TRUNC(SYSDATE, 'MM'), TRUNC(SYSDATE, 'Q'), TRUNC(SYSDATE, 'YYYY'),
       ROUND(SYSDATE, 'MONTH'),
       EXTRACT(YEAR FROM SYSDATE), EXTRACT(HOUR FROM SYSTIMESTAMP),
       SYSDATE + 7, SYSDATE - 1/24,                         -- date arithmetic in days
       SYSDATE + INTERVAL '30' MINUTE,
       NUMTODSINTERVAL(90, 'MINUTE'), NUMTOYMINTERVAL(18, 'MONTH'),
       FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'Asia/Kolkata') AT TIME ZONE 'UTC',
       DBTIMEZONE, SESSIONTIMEZONE
FROM dual;
-- 23ai: TIME_BUCKET, interval aggregation (SUM/AVG of INTERVALs)

8.5 Conversion Functions

Format models: YYYY, RRRR, MM, MON, MONTH, DD, DY, DAY, HH24, MI, SS, FF, AM, TZH, TZR, Q, WW, IW, J. Use FM to remove padding and FX for exact matching.

NLS parameters: NLS_DATE_FORMAT, NLS_LANGUAGE, NLS_TERRITORY, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_COMP.

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
       TO_CHAR(SYSDATE, 'Day, DDth Month YYYY'),
       TO_CHAR(1234567.891, 'FM9,99,99,999.00'),
       TO_CHAR(1234.5, 'L9999.99', 'NLS_CURRENCY=₹'),
       TO_DATE('24-10-2025', 'DD-MM-YYYY'),
       TO_TIMESTAMP('2025-10-24 10:30:00.123', 'YYYY-MM-DD HH24:MI:SS.FF'),
       TO_NUMBER('1,234.50', '9,999.99'),
       CAST('123' AS NUMBER),
       TO_NUMBER('abc' DEFAULT 0 ON CONVERSION ERROR),            -- 12.2+
       VALIDATE_CONVERSION('2025-13-01' AS DATE, 'YYYY-MM-DD')    -- 0 = invalid
FROM dual;

8.6 NULL-Related and Conditional Functions

SELECT NVL(commission_pct, 0),
       NVL2(commission_pct, 'Has commission', 'None'),
       NULLIF(salary, 0),
       COALESCE(commission_pct, bonus_pct, 0),
       LNNVL(commission_pct > 0.2),          -- TRUE when the condition is FALSE or UNKNOWN
       DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
FROM employees;

8.7 Other Useful Functions

SELECT USER, UID, SYS_CONTEXT('USERENV', 'SESSION_USER'), SYS_CONTEXT('USERENV', 'CON_NAME'),
       SYS_CONTEXT('USERENV', 'IP_ADDRESS'), SYS_GUID(), ORA_HASH('abc'),
       STANDARD_HASH('abc', 'SHA256'), DUMP('abc'), VSIZE('abc'),
       USERENV('LANG'), ORA_INVOKING_USER
FROM dual;