SQL Single-Row Functions: Customize Output Row by Row

A single-row function runs once for every row the query touches and returns one value for each. If a query reads seven rows, a single-row function gets called seven times. UPPER, LOWER, TRIM, ROUND, and TO_CHAR are all in this family.

SELECT customer_name,
       UPPER(customer_name) AS name_upper
FROM   customers;

That query returns seven rows — one per customer — with an uppercased version of the name alongside the original. The row count is unchanged.

A multi-row function, also called an aggregate, works differently. It reads every row and returns a single summary value. COUNT(*), SUM(order_amount), and AVG(credit_limit) are all aggregates. They collapse many rows into one.

The distinction matters when you write a query, because the two families live in different parts of the SQL statement. Single-row functions can appear almost anywhere, including WHERE. Aggregates are restricted to SELECT and HAVING — try to put one in a WHERE clause and the database stops you with an error.

UPPER takes a string and returns the same string with every letter capitalised. UPPER('hello') returns HELLO. Numbers, spaces, and punctuation are untouched.

SELECT customer_name,
       UPPER(customer_name) AS name_upper
FROM   customers;

Alice Corp becomes ALICE CORP. Bob Traders becomes BOB TRADERS. The rest of the row is unchanged.

In production, UPPER is most often used to normalise data before comparing it. Suppose you store customer names in whatever case the user typed, and you want to search for "alice corp" reliably. Instead of hoping the casing matches, write WHERE UPPER(customer_name) = 'ALICE CORP'. Now the comparison works regardless of how the data was entered.

One caveat worth remembering: wrapping a column in UPPER disables any index on that column. If the table is large and the query is slow, that is usually why. The fix is a function-based index, but that is a conversation for a performance-tuning discussion.

LOWER is the mirror of UPPER. It takes a string and returns the same string with every letter lowercased. LOWER('HELLO') returns hello.

SELECT customer_name,
       LOWER(customer_name) AS name_lower
FROM   customers;

The typical uses are similar to UPPER — normalising data for comparison and giving reports a consistent look. Lowercase is common for email addresses, usernames, and URLs, where case differences should be treated as identical. If your login system stores emails in mixed case and compares them literally, someone who types their email with different capitalisation will not be able to log in. Wrapping both sides in LOWER fixes that class of bug entirely.

TRIM removes spaces from the beginning and the end of a string. TRIM(' Alice Corp ') returns Alice Corp. Spaces inside the string are left alone — only the leading and trailing ones go.

SELECT TRIM(customer_name) AS trimmed_name
FROM   customers;

The reason this matters more than it seems: stray whitespace is the most common source of "the data is there but the query cannot find it" bugs. When a record comes in through a CSV import, a copy-paste, or an API that did not clean its payload, whitespace almost always gets attached. A query that expects 'Alice Corp' will not match a stored value of ' Alice Corp', and there is nothing visually different between the two in a spreadsheet.

TRIM can also take a character to strip. TRIM('*' FROM '*hello*') returns hello. That form is useful when data uses a specific placeholder or padding character around real values.

CONCAT joins strings into one. CONCAT('Alice', ' ', 'Corp') returns Alice Corp. The function accepts two or three arguments depending on the database. For more complex joins, chain multiple CONCAT calls or use the || operator.

SELECT CONCAT(customer_name, ' (', region, ')') AS display_name
FROM   customers;

That produces labels like Alice Corp (North) and Bob Traders (South) — a formatted string built from two columns plus literal text. The || operator does the same thing: customer_name || ' (' || region || ')'. Most developers prefer one or the other based on the database they use most. Both end up at the same place.

In real work, CONCAT shows up most often for building display names, formatting addresses, or creating readable log entries. Anywhere you need to blend a value from a column with fixed text.

LENGTH returns the number of characters in a string, including spaces. LENGTH('hello') returns 5. LENGTH(' hello ') returns 9.

SELECT customer_name,
       LENGTH(customer_name) AS name_length
FROM   customers;

Where it earns its keep is validation. If a column is defined as VARCHAR2(20) and someone is about to insert a value of length 45, catching it before the insert fails gives you a much better error message than the database truncating or rejecting the row. On the read side, LENGTH is a quick way to spot data anomalies — a customer name with length 1 is almost always a placeholder or a broken import.

Note that LENGTH counts characters, not bytes. For multi-byte character sets, a single character might occupy more than one byte, so LENGTH and LENGTHB can return different numbers on the same string.

SUBSTR takes a starting position and a length, and returns the characters in between. SUBSTR('hello world', 1, 5) returns hello — starting at position 1 (the "h"), for 5 characters.

SELECT customer_id,
       SUBSTR(customer_id, 2, 3) AS id_numeric_part
FROM   customers;

On C001, that returns 001 — the numeric part of the ID. On C007, it returns 007. This is the pattern behind extracting area codes from phone numbers, usernames from email addresses, or a country code from an international reference number.

Positions in SQL strings are 1-based, not 0-based. SUBSTR('hello', 1, 1) returns the first character, not the second. That trips up developers who are used to languages like Python and JavaScript, where indexing starts at zero.

ROUND takes a number and a decimal-place count, and returns the number rounded to that many decimals. ROUND(123.456, 2) returns 123.46.

SELECT order_id,
       order_amount,
       ROUND(order_amount, 2) AS rounded_amount
FROM   orders;

On our data, order 1005 has an amount of 3200.999. With ROUND to two decimals, it becomes 3201.00. Order 1007's 9000.0 stays 9000.0.

The use case is not obscure. Prices, percentages, tax calculations, and any other value a human is going to read eventually need a fixed precision. ROUND is the standard way to get there. Pass a second argument of 0 to round to whole numbers, or a negative number to round to the nearest ten, hundred, or thousand — that second form is common in dashboards where you want a chart axis that is easier to read.

CEIL rounds a number up to the next whole number. CEIL(4.3) returns 5. CEIL(4.9) also returns 5. CEIL(4.0) returns 4, because the number is already whole.

SELECT order_id,
       CEIL(order_amount / 1000) AS thousands_ceil
FROM   orders;

Order 1001 is 12000.456. Divided by 1000, that is 12.0004. CEIL of that is 13. If you are bucketing orders into thousand-unit buckets and you want the bucket to be big enough to hold the value, CEIL gives you the answer that does not understate.

The classic use is any "how many containers do I need" question. A box holds ten items, you have 47 items, and 47 divided by 10 is 4.7 — which means you need 5 boxes, not 4. CEIL gets you to the right number without any special-case logic.

FLOOR rounds a number down to the previous whole number. FLOOR(4.7) returns 4. FLOOR(4.1) also returns 4.

SELECT order_id,
       FLOOR(order_amount / 1000) AS thousands_floor
FROM   orders;

Order 1005 is 3200.999. Divided by 1000, that is 3.200999. FLOOR of that is 3. If you are counting how many complete thousand-unit buckets the order fills, FLOOR gives you the conservative answer.

FLOOR and CEIL are the two directional rounders. ROUND is the closest-neighbour rounder. TRUNC is the "just drop the decimals" version that does not care about direction. Knowing which one you want before you write the query saves a round of debugging later.

MOD returns what is left over after dividing one number by another. MOD(10, 3) returns 1, because 10 divided by 3 leaves a remainder of 1.

SELECT order_id,
       MOD(order_id, 2) AS even_check
FROM   orders;

That returns 1 for odd order ids and 0 for even ones. Divisibility checks like this are the most common use — MOD(n, 2) = 0 means even, MOD(n, 5) = 0 means the number is a multiple of 5.

MOD also shows up in round-robin and cycle logic. If you want to route orders to one of three support queues based on the order id, MOD(order_id, 3) gives you 0, 1, or 2 — a clean three-way split. Same trick works for scheduling, hashing, and any place where you need to cycle through a fixed set of options.

NVL takes two arguments. If the first is not NULL, it returns the first. If the first is NULL, it returns the second. The name is Oracle shorthand for "null value."

SELECT customer_name,
       NVL(credit_limit, 0) AS limit_with_default
FROM   customers;

Fiona Mart has a NULL credit limit. With NVL, she comes back with a value of 0 instead. Everyone else still has their real credit limit.

Why this matters: NULL propagates. Any arithmetic involving NULL returns NULL. Any function called with NULL returns NULL. Any comparison with NULL evaluates to "unknown" rather than true or false. A single NULL in a column can silently wipe out a total, distort an average, or break a downstream calculation. Replacing NULL with a sensible default is often the difference between a report that works and one that quietly returns wrong numbers.

NVL is Oracle-specific. The portable equivalent is COALESCE. If you are writing SQL that might run on more than one database, COALESCE is the safer choice.

The functional difference is in how many values each one can check. NVL takes exactly two arguments — the value to test, and the fallback. COALESCE takes any number of arguments and returns the first one that is not NULL.

SELECT COALESCE(credit_limit, order_amount, 0) AS effective_value
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

That query tries credit_limit first. If it is NULL, it tries order_amount. If that is also NULL, it falls back to 0. NVL cannot do this — you would have to nest two NVL calls together to achieve the same result.

The other difference is portability. COALESCE is part of the SQL standard and works in every major database, including Oracle. NVL is Oracle-specific. If a query is going to move between systems, COALESCE is the function to reach for. If you are deep in an Oracle codebase and everyone else is already using NVL, that is fine too.

One practical note: COALESCE short-circuits. It stops evaluating as soon as it finds a non-NULL value, which means the remaining arguments are not computed. That matters when the arguments involve expensive function calls.

TO_CHAR converts a date into a string using a format mask. You tell it what shape you want the output to take, and it hands back the text.

SELECT order_id,
       TO_CHAR(order_date, 'YYYY-MM-DD')  AS date_only,
       TO_CHAR(order_date, 'DD Mon YYYY') AS friendly_date,
       TO_CHAR(order_date, 'Month YYYY')  AS month_year
FROM   orders;

Order 1001's date of 2024-01-15 renders as 2024-01-15 in the first column, 15 Jan 2024 in the second, and January 2024 in the third. Same date, three different presentations, chosen by the format mask.

The two places this matters most are reports and time-based grouping. A report that shows raw date values is technically correct but hard to scan. Wrapping the date in TO_CHAR gives the reader something they can process in a glance. And when you want to group orders by month, TO_CHAR(order_date, 'YYYY-MM') gives you a consistent string key that sorts and groups cleanly.

Note that TO_CHAR turns the date into a string. Once it is a string, it no longer behaves like a date for comparisons or arithmetic. Do your date calculations first, then convert for display.

TO_DATE is the reverse of TO_CHAR. It takes a string and a format mask, and returns a proper date value the database can work with.

SELECT TO_DATE('2024-11-26', 'YYYY-MM-DD') AS converted_date
FROM   dual;

The string '2024-11-26' becomes a real date. Once it is a date, it can be sorted correctly, compared against other dates, and used in date arithmetic like order_date + 30.

Without conversion, string dates are just strings. Comparisons are lexicographic rather than chronological, which produces wrong results in surprising ways. '2024-09-01' sorts after '2024-10-01' as a string because "9" is bigger than "1" at the third character. As a date, September correctly comes before October. Conversion is what makes the difference.

This function is essential when you are importing data. CSV files, Excel exports, and external APIs typically deliver dates as strings. Until you pass them through TO_DATE with the matching format mask, SQL has no way of knowing they are dates and no way of treating them as such.

TO_NUMBER takes a string that looks like a number and returns it as an actual numeric value. TO_NUMBER('1234.56') becomes the number 1234.56.

SELECT TO_NUMBER('1234.56') AS converted_number
FROM   dual;

Numbers stored as text cannot be added, subtracted, averaged, or compared numerically. The string '100' is less than the string '99' because "1" comes before "9" in character order — even though 100 is the larger number. Once converted with TO_NUMBER, the comparison works the way anyone would expect.

You see this most often with imported data. A CSV file that contains an amount column will load as text unless you convert it, and any query that tries to sum those amounts will either error out or produce nonsense. TO_NUMBER with the right format mask fixes the data at read time. On the write side, the database may do implicit conversion when you insert a numeric string into a numeric column, but relying on that is riskier than being explicit.

TRUNC cuts off a number or a date at a specific point, without any rounding. TRUNC(123.456, 2) returns 123.45 — the digits after position 2 are simply discarded, not rounded.

SELECT order_amount,
       TRUNC(order_amount, 2) AS truncated
FROM   orders;

Order 1005's amount is 3200.999. TRUNC to two decimals gives 3200.99. ROUND to two decimals gives 3201.00. The difference looks small until you are working with money and the accumulated discrepancy across a million rows adds up to real numbers.

TRUNC is the function of choice in financial contexts where rounding direction must be controlled by policy rather than by the database. When a report says "values are truncated to two decimals," TRUNC is what makes that true.

On dates, TRUNC strips the time component. TRUNC(SYSDATE) returns today's date at midnight. That is how you compare dates when you do not care about the time, or when you want to group by day without an implicit timezone surprise.

REPLACE swaps every occurrence of one substring with another. It takes three arguments — the original string, the substring to find, and the substring to replace it with.

SELECT REPLACE(customer_name, 'Corp', 'Corporation') AS formal_name
FROM   customers;

Alice Corp becomes Alice Corporation. The replacement happens for every matching occurrence in the string, and it happens for every row in the result.

The most common real-world use is bulk cleanup. If a product name changed, or a term is outdated in customer records, or an imported file uses a different abbreviation than your standard, REPLACE fixes it in one pass. You do not need to write a row-by-row update loop. Just wrap the column and let the function handle it.

One caution — REPLACE is case-sensitive. REPLACE(name, 'corp', 'Corporation') will not match "Corp". If your data has inconsistent case, you may need to combine REPLACE with UPPER or LOWER on the input, or write the replacement in multiple passes.

LPAD adds characters to the left side of a string until it reaches a specified length. LPAD('123', 5, '0') returns 00123 — two zeros added to the left to bring the length from 3 up to 5.

SELECT customer_id,
       LPAD(customer_id, 8, '0') AS padded_id
FROM   customers;

C001 becomes 0000C001. Every id in the table now occupies exactly 8 characters.

The reason to do this is almost always formatting. Fixed-width file exports require every column to be exactly the same length. Reports that put values in columns look messy if the widths vary. Padding ids or codes with leading zeros is standard practice in banking, invoicing, and any system that generates reference numbers. LPAD gives you that consistency in one function call, without any client-side formatting code.

If the padding character is not specified, LPAD pads with spaces by default. That is useful for producing column-aligned plain-text output.

RPAD is the mirror of LPAD. It adds padding characters to the right side of a string until it reaches the target length. RPAD('Hello', 10, '*') returns Hello*****.

SELECT customer_name,
       RPAD(customer_name, 20, '.') AS dot_filled
FROM   customers;

Alice Corp becomes Alice Corp.......... — ten dots filled to reach twenty characters.

Both LPAD and RPAD exist because fixed-width output is a real requirement in a lot of systems. Legacy mainframe feeds, COBOL-style interfaces, and reporting tools that expect exact column widths all need this. On the read side, RPAD is also a quick way to make text-based output line up in a terminal or in a text file that someone is going to open in a text editor.

SIGN tells you the direction of a number. It returns 1 if the number is positive, -1 if it is negative, and 0 if the number is exactly zero.

SELECT order_amount - 10000 AS difference,
       SIGN(order_amount - 10000) AS direction
FROM   orders;

For every order, the query returns the difference from 10,000 and a flag: 1 if the order is above the threshold, -1 if it is below, 0 if it is exactly at 10,000.

The reason this is useful is that it collapses a three-way decision into a single number. In finance, a positive value is a credit and a negative value is a debit. In a dashboard, sign flags let you split data into categories without writing a full CASE expression. It is a small function, but it earns its place in any codebase that deals with signed quantities.

NVL2 is a three-argument function that handles both cases of a NULL check. You give it a value to test, a value to return if the result is not NULL, and a value to return if the result is NULL.

SELECT customer_name,
       NVL2(credit_limit, 'Has limit', 'No limit') AS limit_status
FROM   customers;

That returns Has limit for every customer with a credit limit and No limit for Fiona Mart, who has NULL. Two different outputs depending on which side of the NULL check the value lands on.

It is the compact alternative to a full CASE expression when the only thing you are branching on is whether a value exists. NVL — the simpler sibling — only lets you replace NULL with a single fallback value. NVL2 lets you produce a label, a message, or a different default for each case. If you are generating a report and want the output to clearly mark which rows are complete and which are missing data, NVL2 is the cleanest tool for the job.

NVL2 is Oracle-specific. In other databases, the equivalent is a CASE expression or a combination of COALESCE and NULLIF.

EXTRACT pulls a single part out of a date value — the year, the month, the day, the hour, the minute, or the second. Each call returns one number.

SELECT order_id,
       EXTRACT(YEAR  FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       EXTRACT(DAY   FROM order_date) AS order_day
FROM   orders;

Order 1001's date is 2024-01-15. The three calls return 2024, 1, and 15 respectively.

The most common uses are filtering and grouping. To keep only March orders, write WHERE EXTRACT(MONTH FROM order_date) = 3. To summarise by year, write GROUP BY EXTRACT(YEAR FROM order_date). Both read more clearly than equivalent string manipulation, and both work regardless of the date's display format — because EXTRACT operates on the underlying date value, not on a formatted string.

Some databases support additional parts — QUARTER, WEEK, DAYOFWEEK — which makes EXTRACT a Swiss-army-knife for date-based reporting. Check your specific database for the full list.

CONVERT changes a value from one data type to another. You pass in a target type and the value to convert, and it returns the value in the new type.

SELECT CONVERT(VARCHAR, 123) AS text_value,
       CONVERT(INT, '456')    AS number_value
FROM   dual;

The first converts the number 123 into the string '123'. The second converts the string '456' into the number 456.

You reach for CONVERT when a function expects one type but you have another. String functions like UPPER and SUBSTR require string input, so if you have a numeric column and want to slice it, convert first. Date arithmetic requires date input, so if you have a text date you need to convert before adding days to it.

CONVERT is the SQL Server name for this operation. Oracle uses TO_CHAR, TO_DATE, and TO_NUMBER for the specific conversions. PostgreSQL uses :: or CAST. They all do the same thing — change the type — just with different syntax. The portable form across every database is CAST(value AS type).