SQL Functions: Strings, Dates, Type Casting and Arithmetic

Raw data rarely arrives clean. Order references come with stray spaces, names in random case, amounts as text with thousands separators, and timestamps that need turning into days and months. This lesson covers the scalar functions you use to clean and reshape values row by row, and the type and arithmetic rules that decide whether your metrics are right. Function names differ more between engines than any other part of SQL, so each section ends with a translation table.

Sample data

A raw order extract as it might land in a staging table, before cleaning.

CREATE TABLE raw_orders (
  order_ref     TEXT,
  customer_name TEXT,
  email         TEXT,
  ordered_at    TIMESTAMP,
  shipped_at    TIMESTAMP,
  amount_text   TEXT,          -- amount as received from the source system
  quantity      INT,
  unit_price    NUMERIC(10, 2)
);

INSERT INTO raw_orders VALUES
  ('  ord-1001 ', '  asha PATEL ', 'Asha.Patel@Example.com', '2025-06-01 09:15', '2025-06-03 17:40', '120.50',   2,  60.25),
  ('ORD-1002',    'ben carter',    'ben@example.co.uk',      '2025-06-03 23:05', '2025-06-04 08:00', '35.5',     1,  35.50),
  ('ord-1003 ',   'Chen  Wei',     'chen.wei@example.sg',    '2025-06-30 14:00', NULL,               '1,200.00', 3, 400.00),
  ('ORD-1004',    'diana lopez',   NULL,                     '2025-07-01 00:30', '2025-07-06 12:00', 'n/a',      7,   2.99),
  ('ORD-1005',    'Ethan Brown',   'ethan@example.com',      '2025-12-31 22:10', '2026-01-02 09:00', '64.99',    1,  64.99);

String functions: CONCAT, SUBSTRING, TRIM and friends

String functions clean, split and combine text. In pipelines they mostly do three jobs: normalise values so they match (trim, fix case), extract a part (a domain from an email, a prefix from a code) and build keys or labels from several columns.

Cleaning: TRIM, UPPER, LOWER, INITCAP and REPLACE

SELECT order_ref,
       UPPER(TRIM(order_ref))                              AS clean_ref,
       INITCAP(TRIM(customer_name))                        AS clean_name,
       REGEXP_REPLACE(TRIM(customer_name), '\s+', ' ', 'g') AS single_spaced
FROM raw_orders
ORDER BY clean_ref;

• TRIM(s) removes leading and trailing spaces; LTRIM and RTRIM remove one side. In PostgreSQL TRIM(BOTH 'x' FROM s) removes other characters too. TRIM does not touch spaces inside the string, which is why “Chen Wei” keeps its double space until the regular expression collapses it.
• UPPER and LOWER exist everywhere. INITCAP (title case) exists in PostgreSQL, Snowflake, Oracle and BigQuery, but not in MySQL or SQL Server. Title case is naive: it produces “Mcdonald” from “mcdonald”.
• REPLACE(s, from, to) replaces every occurrence of a fixed substring. For patterns, use REGEXP_REPLACE.

Extracting: SUBSTRING, LEFT, RIGHT, POSITION and SPLIT_PART

SELECT email,
       LOWER(SPLIT_PART(email, '@', 2))                      AS domain,
       SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS local_part,
       RIGHT(TRIM(order_ref), 4)                             AS ref_number,
       LENGTH(TRIM(order_ref))                               AS ref_length
FROM raw_orders
ORDER BY order_ref;

The odd row order is deliberate: the query sorts on the raw order_ref, so leading spaces and lower case decide the order. This database uses the byte-wise C collation, where a space sorts before letters and upper case before lower case, which puts 'ord-1003 ' last. A database with a linguistic collation such as en_US orders the same values differently. Always sort and join on the cleaned value.

• SQL positions start at 1, not 0. SUBSTRING(s FROM 2 FOR 3) (standard) and SUBSTRING(s, 2, 3) (PostgreSQL, MySQL, SQL Server) both return three characters starting at the second.
• POSITION('@' IN s) returns the 1-based position, or 0 if not found. A missing @ makes POSITION(...) - 1 equal to -1, so guard it with a CASE in real code.
• SPLIT_PART(s, delimiter, n) returns the nth piece. It is PostgreSQL and Snowflake syntax; BigQuery returns an array from SPLIT(s, '@'), and SQL Server needs STRING_SPLIT or CHARINDEX arithmetic.
• LENGTH counts characters in PostgreSQL. SQL Server’s LEN ignores trailing spaces, and MySQL’s LENGTH counts bytes (use CHAR_LENGTH for characters), which differ for accented or non-Latin text.

Combining: CONCAT, || and CONCAT_WS

SELECT CONCAT(UPPER(TRIM(order_ref)), '-', quantity)  AS line_key,
       TRIM(order_ref) || ' / ' || email              AS with_operator,
       CONCAT_WS(' | ', TRIM(customer_name), email)   AS with_separator
FROM raw_orders
WHERE TRIM(order_ref) IN ('ORD-1002', 'ORD-1004');

The || operator returns NULL when any part is NULL, while PostgreSQL’s CONCAT and CONCAT_WS skip NULL arguments. CONCAT also converts numbers to text for you. When you build a surrogate or hash key from several columns, wrap each nullable column in COALESCE and use a separator: without a separator, ('ab', 'c') and ('a', 'bc') produce the same key.

Dialect translation

NULL behaviour also differs: MySQL’s CONCAT returns NULL if any argument is NULL, whereas PostgreSQL and SQL Server skip NULL arguments.

Pitfalls

• Joining on uncleaned text: 'ORD-1001' and ' ord-1001 ' never match. Normalise both sides in staging, once.
• Applying functions to a column in WHERE or JOIN (WHERE UPPER(email) = ...) prevents ordinary index use and partition pruning. Store a cleaned column instead.
• Counting bytes instead of characters with MySQL LENGTH.
• Building keys by concatenating nullable columns without COALESCE and a separator.

In interviews

String questions are usually data-cleaning tasks: “extract the domain from an email”, “standardise these names”, “find codes that do not match a pattern”. Name the function you would use in the interviewer’s dialect, handle NULL and the “delimiter not found” case, and say that you would clean once in a staging layer.

Date and time functions

Dates drive almost every metric: daily revenue, time to ship, monthly active users. The basic toolkit is getting the current date, extracting parts, adding intervals, computing differences and truncating to a period. Truncation and calendars get a full treatment in the time-series lesson; this section covers the building blocks.

Types first

Store event times as an absolute instant (UTC or a time-zone-aware type) and convert to local time only for reporting. “Which day did this order happen on?” depends on the time zone: order 1002 at 23:05 UTC is already 4 June in India.

Extracting parts and formatting

SELECT TRIM(order_ref)                     AS ref,
       ordered_at,
       CAST(ordered_at AS DATE)            AS order_day,
       EXTRACT(YEAR FROM ordered_at)       AS yr,
       EXTRACT(MONTH FROM ordered_at)      AS mon,
       EXTRACT(ISODOW FROM ordered_at)     AS iso_weekday,
       TO_CHAR(ordered_at, 'Dy DD Mon')    AS label,
       DATE_TRUNC('month', ordered_at)     AS month_start
FROM raw_orders
ORDER BY ordered_at;

ISODOW numbers Monday as 1 and Sunday as 7. Plain DOW in PostgreSQL numbers Sunday as 0, and other engines have their own conventions (SQL Server’s DATEPART(weekday, ...) even depends on the DATEFIRST setting). Check before you hard-code day numbers.

Adding intervals and computing differences

SELECT TRIM(order_ref)                                  AS ref,
       ordered_at + INTERVAL '30 days'                  AS return_deadline,
       shipped_at - ordered_at                          AS time_to_ship,
       CAST(shipped_at AS DATE) - CAST(ordered_at AS DATE) AS calendar_days,
       ROUND(EXTRACT(EPOCH FROM shipped_at - ordered_at) / 3600, 1) AS hours_to_ship
FROM raw_orders
ORDER BY ordered_at;

Two kinds of “difference” are in this table, and they answer different questions. Order 1002 shipped 8 hours 55 minutes after it was placed, but on the next calendar day, so it counts as 1 calendar day. Order 1005 took under 35 hours but crossed two midnights. Decide which one the business means by “days to ship”.

Month arithmetic clamps to the end of the month:

SELECT DATE '2025-01-31' + INTERVAL '1 month' AS one_month_later,
       DATE '2025-03-01' - DATE '2025-02-01'  AS days_in_feb;

In PostgreSQL, date - date returns an integer number of days, timestamp - timestamp returns an INTERVAL, and adding an interval to a date returns a timestamp.

Dialect translation

Note the argument order: MySQL’s DATEDIFF(end, start) and BigQuery’s DATE_DIFF(end, start, part) put the later date first, while SQL Server’s and Snowflake’s DATEDIFF(part, start, end) put it last. SQL Server and Snowflake count boundaries crossed, so DATEDIFF(year, '2025-12-31', '2026-01-01') is 1.

Pitfalls

• BETWEEN on timestamps misses the last day; use half-open ranges (covered in the previous lesson).
• Wrapping the column in a function in WHERE (WHERE EXTRACT(YEAR FROM ordered_at) = 2025) blocks pruning. Filter on a range of the raw column.
• Mixing time zones: comparing a UTC timestamp with a local-time literal, or truncating to a day in UTC when the business reports in local time.
• Formatting dates as text too early. TO_CHAR(d, 'Mon YYYY') sorts alphabetically (“Apr” before “Jan”). Keep a real date for sorting and format at the end.

In interviews

Expect “days between two dates”, “orders in the last 30 days” and “group by month”. A strong answer uses the right function for the stated dialect, mentions time zones and the boundary-counting behaviour of DATEDIFF, and filters with a sargable range.

CAST and data type conversion

Every value has a type, and many bugs are type bugs: text that should be a number, a timestamp compared with a string, or an integer that silently drops decimals. CAST(value AS type) converts explicitly; PostgreSQL also offers the shorthand value::type.

Explicit and implicit conversion

SELECT CAST('2025-06-01' AS DATE)                 AS text_to_date,
       '42'::INT + 1                               AS text_to_int,
       CAST(ordered_at AS DATE)                    AS ts_to_date,
       CAST(unit_price AS INT)                     AS numeric_to_int,
       TO_DATE('03/06/2025', 'DD/MM/YYYY')         AS parsed_uk_date
FROM raw_orders
WHERE TRIM(order_ref) = 'ORD-1004';

• Casting a timestamp to a date drops the time.
• Casting 2.99 to an integer rounds in PostgreSQL (giving 3) but truncates in SQL Server (giving 2). If you mean “drop the decimals”, say so with TRUNC or FLOOR.
• For text dates in a non-ISO format, parse with an explicit format: TO_DATE (PostgreSQL, Snowflake, Oracle), STR_TO_DATE (MySQL), PARSE_DATE (BigQuery) or CONVERT with a style number (SQL Server). Guessing between 03/06 as 3 June and 6 March is exactly the bug to avoid.
• Engines also convert implicitly in some comparisons. MySQL converts freely ('10abc' = 10 can be true), PostgreSQL much less so. Write explicit casts in pipeline code so behaviour does not depend on the engine.

When a cast fails

A plain CAST stops the whole query on the first bad value:

-- ERROR: invalid input syntax for type numeric: "1,200.00"
SELECT CAST(amount_text AS NUMERIC(10, 2)) FROM raw_orders;

In a pipeline you usually want to keep the good rows and quarantine the bad ones. The tools differ by engine:

In DuckDB, TRY_CAST handles the bad rows directly:

SELECT amount_text,
       TRY_CAST(REPLACE(amount_text, ',', '') AS DECIMAL(10, 2)) AS amount
FROM (VALUES ('120.50'), ('1,200.00'), ('n/a')) AS t(amount_text);

In PostgreSQL 16, combine pg_input_is_valid with CASE, and keep the original text so you can report what failed:

SELECT TRIM(order_ref) AS ref,
       amount_text,
       CASE WHEN pg_input_is_valid(REPLACE(amount_text, ',', ''), 'numeric')
            THEN CAST(REPLACE(amount_text, ',', '') AS NUMERIC(10, 2))
       END AS amount,
       NOT pg_input_is_valid(REPLACE(amount_text, ',', ''), 'numeric') AS is_bad_amount
FROM raw_orders
ORDER BY UPPER(TRIM(order_ref));

Choosing numeric types

SELECT 0.1::FLOAT8 + 0.2::FLOAT8 AS float_sum,
       0.1::NUMERIC + 0.2::NUMERIC AS numeric_sum;

Never store money in a float: summing millions of float amounts drifts by fractions of a cent, and reconciliation against the source system fails.

Pitfalls

• Casting in a join condition (ON a.id = CAST(b.id AS INT)) hides a modelling problem and usually prevents index use. Fix the types in staging.
• Relying on implicit string-to-date conversion with an ambiguous format.
• Using TRY_CAST and never looking at the NULLs it produced. Count and log them.
• Shrinking types (VARCHAR(10), NUMERIC(5,2)) without checking the longest value; the cast fails or, in some engines, truncates.

In interviews

You might be asked how to load a column that has a few bad values. Mention the safe-cast function for the dialect, keeping the raw value, counting rejects and routing them to a quarantine table rather than silently converting them to NULL. “Why not FLOAT for money?” is also common.

Arithmetic and rounding

Arithmetic in SQL looks obvious, but integer division, rounding rules and NULL propagation regularly produce wrong ratios and percentages.

Operators and integer division

SELECT 7 / 2         AS int_division,
       7 / 2.0       AS decimal_division,
       7 % 3         AS modulo,
       -7 / 2        AS negative_int_division,
       quantity * unit_price AS line_total
FROM raw_orders
WHERE TRIM(order_ref) = 'ord-1003';

When both operands are integers, PostgreSQL and SQL Server perform integer division, discarding the remainder (towards zero, so -7 / 2 is -3). MySQL, Snowflake, BigQuery, Oracle and DuckDB return a decimal 3.5 instead. This is the classic bug in ratio metrics:

SELECT COUNT(*) FILTER (WHERE shipped_at IS NOT NULL) / COUNT(*)         AS wrong_ratio,
       COUNT(*) FILTER (WHERE shipped_at IS NOT NULL) * 1.0 / COUNT(*)   AS right_ratio,
       ROUND(100.0 * COUNT(*) FILTER (WHERE shipped_at IS NOT NULL) / COUNT(*), 1) AS pct_shipped
FROM raw_orders;

Four of five orders shipped, but integer division returns 0. Multiply by 1.0 (or 100.0 for a percentage) before dividing, or cast one side to NUMERIC. (FILTER is PostgreSQL’s way of counting a subset; the aggregations lesson shows the portable CASE form.) MySQL and BigQuery offer DIV for deliberate integer division, and DuckDB uses //.

Rounding functions

How halves round depends on the type. In PostgreSQL, NUMERIC rounds half away from zero, while DOUBLE PRECISION follows the platform, which is round-half-to-even on most systems:

SELECT ROUND(2.5::NUMERIC) AS numeric_half,
       ROUND(2.5::FLOAT8)  AS float_half,
       ROUND(3.5::FLOAT8)  AS float_three_half,
       ROUND(-2.5::NUMERIC) AS negative_half;

DuckDB, by contrast, rounds a DOUBLE 2.5 to 3. When reconciling totals with another system, compare rounding rules, not only formulas.

Round at the end

Round only the value you present, not the intermediate steps. Rounding each line before summing gives a different total from rounding the sum, and the difference grows with row count. Also prefer ROUND(SUM(x), 2) over SUM(ROUND(x, 2)) unless the business rule says each line is rounded (as invoices often are, in which case follow the rule exactly).

Other useful functions

ABS, POWER, SQRT, LN and EXP are available almost everywhere. GREATEST(a, b, ...) and LEAST(...) pick the largest or smallest of several columns in PostgreSQL, MySQL, Snowflake, BigQuery and SQL Server 2022 or later. Watch the NULL behaviour: PostgreSQL’s GREATEST ignores NULLs, while MySQL and Snowflake return NULL if any argument is NULL.

Pitfalls

• Integer division in ratios and percentages.
• Division by zero: guard with NULLIF(denominator, 0).
• NULL in any operand makes the result NULL: quantity * unit_price is NULL if either is missing.
• Rounding intermediate values, then wondering why totals do not reconcile.
• Comparing floats with =. Compare with a tolerance (ABS(a - b) < 0.0001) or use NUMERIC.

In interviews

“Why does my conversion rate show 0?” is integer division. “Why is the total off by a cent?” is rounding order or floats. State the fix in one line (* 1.0 or a cast, round at the end, NUMERIC for money) and mention that engines differ on /.

Practice questions

CASE WHEN POSITION('@' IN email) > 0
     THEN LOWER(SPLIT_PART(email, '@', 2))
END AS domain

Key takeaways

• Clean text once in staging with TRIM, UPPER/LOWER and REPLACE, and join and sort on the cleaned values.
• || returns NULL if any part is NULL; CONCAT skips NULLs in PostgreSQL and SQL Server but not in MySQL. Use COALESCE and a separator for keys.
• Know your engine’s names for date add, date difference and truncation, and their argument order; DATEDIFF counts boundaries crossed.
• Cast explicitly; use TRY_CAST/SAFE_CAST (or pg_input_is_valid in PostgreSQL 16) to quarantine bad values instead of failing or hiding them.
• Integer division truncates in PostgreSQL and SQL Server: multiply by 1.0 before dividing.
• Use NUMERIC for money, round once at the end, and know that halves round differently for NUMERIC and floats.