String, Numeric, Date, and Conversion Functions

Learning outcomes

A scalar function receives one row's values and returns one value for that row. The function call is an expression, so it can appear in the select list, predicates, ordering, and many other expression positions.

A function is a value transformation

SELECT    customer_id,    TRIM(full_name) AS clean_name,    LOWER(email) AS normalized_email,    ROUND(credit_limit, 0) AS rounded_limitFROM customerORDER BY customer_id;

String functions

SELECT    customer_id,    TRIM(full_name) AS clean_name,    UPPER(city) AS city_upper,    LOWER(NULLIF(email, '')) AS normalized_email,    LENGTH(TRIM(full_name)) AS name_length,    SUBSTR(TRIM(full_name), 1, 5) AS name_prefixFROM customerORDER BY customer_id;

Numeric functions and arithmetic

SELECT    p.product_name,    p.unit_price,    s.quantity,    ABS(s.quantity) AS absolute_quantity,    ROUND(p.unit_price * s.quantity, 2) AS gross_amount,    ROUND(p.unit_price * s.quantity * COALESCE(s.discount_rate, 0), 2)        AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;

ROUND is appropriate for display-oriented decimal places, but it does not replace a deliberate monetary storage model. Floating-point values can still have binary representation limits.

Date and time functions in SQLite

SQLite has no dedicated date/time storage class. A project must choose a convention such as UTC ISO-style text, Unix seconds, or Julian day values and use it consistently.

SELECT    sale_id,    sold_at,    DATE(sold_at) AS sold_date,    TIME(sold_at) AS sold_time,    STRFTIME('%Y-%m', sold_at) AS sold_month,    STRFTIME('%w', sold_at) AS weekday_numberFROM saleORDER BY sale_id;

SELECT    customer_id,    joined_at,    DATE(joined_at, '+30 days') AS first_review_date,    ROUND(JULIANDAY('2026-08-05') - JULIANDAY(DATE(joined_at)), 0)        AS membership_daysFROM customerORDER BY customer_id;

Conversion with CAST

SELECT    product_id,    unit_price,    CAST(unit_price AS INTEGER) AS truncated_integer,    CAST(stock_qty AS TEXT) AS stock_textFROM productORDER BY product_id;

CAST(expression AS type) communicates an intended result type. Conversion can truncate, round, reject, or reinterpret data depending on the source value and target engine.

SELECT    TYPEOF('42') AS literal_type,    TYPEOF(CAST('42' AS INTEGER)) AS cast_type,    CAST('42' AS INTEGER) + 8 AS numeric_result,    CAST(49.95 AS INTEGER) AS integer_result;

Nested functions: work from the inside out

SELECT    customer_id,    UPPER(TRIM(full_name)) AS display_name,    COALESCE(LOWER(NULLIF(TRIM(email), '')), '[no email]') AS email_label,    STRFTIME('%Y', DATE(joined_at)) AS joined_yearFROM customerORDER BY customer_id;

Read each expression from the innermost call outward. When nesting becomes difficult to explain, split the work into a subquery or another named query layer.

Functions in predicates can affect access paths

SELECT customer_id, emailFROM customerWHERE LOWER(email) = 'nadia@example.com';

The expression may be correct, but applying a function to every stored value can prevent a normal index on email from being used directly. Solutions include normalized stored values, expression indexes where supported, or a case-insensitive collation chosen deliberately. Index design is covered later in the course.

Practice lab

1. Return trimmed customer names and lowercase email addresses.
2. Return product prices rounded to zero and two decimal places.
3. Return each sale date and month.
4. Convert stock quantities to text and inspect their SQLite types.
5. Create a label containing an uppercase category and product name.

SELECT customer_id, TRIM(full_name), LOWER(email)FROM customer;SELECT product_id, ROUND(unit_price, 0), ROUND(unit_price, 2)FROM product;SELECT sale_id, DATE(sold_at), STRFTIME('%Y-%m', sold_at)FROM sale;SELECT product_id, CAST(stock_qty AS TEXT), TYPEOF(CAST(stock_qty AS TEXT))FROM product;SELECT UPPER(category) || ': ' || product_name AS product_labelFROM product;

Common failures

Assuming function names are portable

The concept may be portable while the spelling and argument order are not.

Cleaning only in the SELECT list

A pretty result does not repair inconsistent stored data.

Depending on implicit conversion

Different engines may choose different coercions or reject the expression.

Using local time without a policy

The same text can be interpreted differently across systems.

Rounding too early

Intermediate rounding can accumulate error and change totals.

Summary and references

• Scalar functions transform one row's input expressions into result values.
• String, numeric, date/time, and conversion functions have distinct type and NULL behavior.
• SQLite date/time work requires a consistent storage convention.
• Explicit casts make intended types visible.
• Nested transformations should remain readable and testable.

References

• SQLite: Built-In Scalar SQL Functions
• SQLite: Date and Time Functions
• SQLite: CAST expressions
• PostgreSQL: Functions and Operators