Single-Row Functions in SQL: Complete Guide with Examples

Quick answer: Learn single-row functions in SQL including character, numeric, date, conversion and general functions such as UPPER, ROUND, NVL, COALESCE and CASE, with examples.

Single-row functions operate on one row at a time and return one result for every row processed. If a query returns 500 rows, a single-row function runs 500 times and produces 500 results.

They are the everyday tools of data cleaning and preparation in SQL. Standardising text, rounding numbers, extracting parts of dates and replacing NULLs are all single-row function work.

In this guide you will learn:

• What single-row functions are

• Character functions

• Numeric functions

• Date functions

• Conversion functions

• General and conditional functions

• Interview questions

What are Single-Row Functions?

A single-row function accepts one or more arguments and returns one value per input row. They can be used in SELECT, WHERE, ORDER BY and most other clauses.

Types of Single-Row Functions

1. Character functions

2. Numeric functions

3. Date functions

4. Conversion functions

5. General functions

1. Character Functions

These work on text values.

Case conversion

SELECT UPPER('data science')   AS upper_case,   -- DATA SCIENCE
       LOWER('DATA SCIENCE')   AS lower_case,   -- data science
       INITCAP('data science') AS title_case;   -- Data Science

These are heavily used to standardise data before comparison, since 'Nagpur' and 'nagpur' will not match otherwise.

LENGTH

SELECT LENGTH('Fireblaze') AS name_length;  -- 9

SUBSTR

Extracts part of a string. The syntax is SUBSTR(string, start_position, number_of_characters).

SELECT SUBSTR('Data Analytics', 1, 4)  AS first_word;  -- Data

Note that SQL string positions start at 1, not 0.

CONCAT

SELECT CONCAT('Fire', 'blaze') AS joined;  -- Fireblaze

-- Most databases also support the || operator
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

TRIM, LTRIM and RTRIM

Remove unwanted spaces, a constant problem in imported data.

SELECT TRIM('   Nagpur   ')  AS cleaned;  -- 'Nagpur'

REPLACE

SELECT REPLACE('9876-543-210', '-', '') AS clean_phone;  -- 9876543210

INSTR

Returns the position of a substring, or 0 if it is not found.

SELECT INSTR('analytics@fireblaze.in', '@') AS at_position;  -- 11

2. Numeric Functions

ROUND

SELECT ROUND(157.678, 2)  AS two_decimals,   -- 157.68
       ROUND(157.678, 0)  AS whole_number,   -- 158
       ROUND(157.678, -2) AS to_hundreds;    -- 200

TRUNC

Cuts the number off rather than rounding it. This distinction is a very common interview question.

SELECT ROUND(157.678, 1) AS rounded,   -- 157.7
       TRUNC(157.678, 1) AS truncated; -- 157.6

MOD

Returns the remainder after division. Useful for finding even or odd values.

SELECT MOD(10, 3) AS remainder;  -- 1

ABS, POWER, SQRT and CEIL/FLOOR

SELECT ABS(-45)      AS absolute_value,  -- 45
       POWER(2, 5)   AS two_to_the_five, -- 32
       SQRT(144)     AS square_root,     -- 12
       CEIL(4.2)     AS round_up,        -- 5
       FLOOR(4.8)    AS round_down;      -- 4

3. Date Functions

Exact names vary between database systems, but the concepts are consistent.

-- Current date and time
SELECT SYSDATE FROM dual;          -- Oracle
SELECT CURRENT_DATE;               -- Standard SQL
SELECT GETDATE();                  -- SQL Server

Adding and comparing dates

-- Add months to a date
SELECT ADD_MONTHS(SYSDATE, 6) AS six_months_later FROM dual;

-- Number of months between two dates
SELECT MONTHS_BETWEEN('2026-12-31', '2026-01-01') AS months_gap FROM dual;

Extracting parts of a date

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

This is the standard way to build monthly or yearly reports.

4. Conversion Functions

These convert values between data types.

-- Format a date for a report
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date FROM dual;

-- Convert a text date from an imported file
SELECT TO_DATE('26-07-2026', 'DD-MM-YYYY') AS real_date FROM dual;

Explicit conversion is safer than relying on the database to convert implicitly, which can behave differently across systems.

5. General and Conditional Functions

These handle NULLs and conditional logic, and they appear constantly in real analytical queries.

NVL and COALESCE

-- NVL replaces NULL with a chosen value (Oracle)
SELECT NVL(discount, 0) AS discount_value FROM orders;

-- COALESCE is standard SQL and accepts multiple fallbacks
SELECT COALESCE(mobile, landline, 'No contact') AS contact
FROM customers;

COALESCE returns the first non-NULL value in its list, which makes it more flexible than NVL.

NULLIF

Returns NULL if two values are equal. Its most practical use is preventing division by zero.

SELECT revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM sales_summary;

CASE

The standard way to write conditional logic in SQL.

SELECT student_name,
       marks,
       CASE
         WHEN marks >= 75 THEN 'Distinction'
         WHEN marks >= 60 THEN 'First Class'
         WHEN marks >= 40 THEN 'Pass'
         ELSE 'Fail'
       END AS result
FROM students;

CASE is portable across all major databases. DECODE does something similar but exists only in Oracle, so CASE is the better habit.

Nesting Functions

Single-row functions can be nested, and the innermost function is evaluated first.

SELECT UPPER(TRIM(SUBSTR(full_name, 1, 10))) AS cleaned_name
FROM customers;

Here the string is trimmed to ten characters, then whitespace is removed, then it is converted to upper case.

Interview Questions on Single-Row Functions

What is the difference between single-row and multi-row functions?

Single-row functions return one value per row and preserve the row count. Multi-row functions aggregate multiple rows into one result.

What is the difference between ROUND and TRUNC?

ROUND rounds to the nearest value based on the following digit. TRUNC simply cuts the number at the specified position without rounding.

What is the difference between NVL and COALESCE?

NVL takes exactly two arguments and is Oracle specific. COALESCE is standard SQL, accepts any number of arguments, and returns the first non-NULL value.

How do you prevent a divide by zero error?

Wrap the denominator in NULLIF. Dividing by NULL returns NULL instead of raising an error.

Can single-row functions be used in a WHERE clause?

Yes. However, applying a function to a column in WHERE can prevent the database from using an index on that column, which may slow the query on large tables.

Final Thoughts

Single-row functions are what turn raw stored data into something you can actually analyse. Real world data arrives with inconsistent casing, stray spaces, text dates and NULLs, and these functions are how you clean it inside SQL rather than exporting it elsewhere.

Focus first on the ones you will use daily: UPPER and LOWER, TRIM, SUBSTR, ROUND, the date extraction functions, COALESCE and CASE. Those cover the large majority of practical work and interview questions.