String and Date Functions

Raw data is rarely fit to display. Names arrive in mixed case, dates are stored as plain text, and reports need readable labels like 'Ada N. — 4 years' instead of raw columns. SQL ships with a built-in toolbox for cleaning text and manipulating dates without exporting to another program. In this lesson you will uppercase labels, measure string length, extract the year from a hire date, and compute how long someone has been with the company. These functions work on every row independently, so they sit naturally in the SELECT list and can be combined with CASE, joins, and aggregates you have already learned. The patterns here are portable across SQLite, PostgreSQL, and MySQL, with only minor syntax differences.

flowchart LR
  I["#quot;Ada Nilsen#quot;"] --> U["UPPER(name)"] --> O["#quot;ADA NILSEN#quot;"]
  I --> L["LENGTH(name)"] --> N["11"]
  style O fill:#0e7490,color:#fff

String functions transform text without changing the original column.

Text functions: cleaning and slicing

SQLite's most useful string functions are UPPER and LOWER for case changes, LENGTH for character count, SUBSTR for extracting a slice, and TRIM for removing leading and trailing spaces. All of them accept a column or literal and return a new string, leaving the original data untouched.

SELECT UPPER(name), LENGTH(name), SUBSTR(name, 1, 3)
FROM employees;

SUBSTR uses 1-based indexing — SUBSTR(name, 1, 3) grabs the first three characters. To pull the initial from a first name you might combine it with INSTR to find the position of the space, then slice accordingly.

Two vertical bars || concatenate strings. name || ' works in ' || city builds a sentence directly inside the query. This is more portable than vendor-specific CONCAT functions and works identically in SQLite, PostgreSQL, and Oracle.

Clean and slice names.

SELECT name,
  UPPER(name) AS loud_name,
  LENGTH(name) AS chars,
  SUBSTR(name, 1, 3) AS initials
FROM employees;

Pattern matching with LIKE

When you need to test whether text contains a substring rather than matching exactly, LIKE is the tool. It uses two wildcards: % matches any sequence of characters (including an empty one), and _ matches exactly one character. WHERE name LIKE 'A%' finds every name starting with A; WHERE role LIKE '%Engineer%' finds Engineer, Senior Engineer, and Engineering Lead.

LIKE is case-insensitive in SQLite but case-sensitive in PostgreSQL, so portable code usually normalises case with UPPER or LOWER before comparing. It is slower than exact equality on large tables because the database cannot use a standard index, but for the small datasets typical of reporting queries it is perfectly fine.

Date functions: treating text like a calendar

The hired_on column is stored as text in YYYY-MM-DD format. SQLite does not have a dedicated DATE type, but its date functions understand that shape perfectly. Pass a column to DATE and you get the date back; pass it to STRFTIME with a format code and you extract just the part you need.

Common codes:

%Y — four-digit year
%m — month 01-12
%d — day 01-31
%J — Julian day number

JULIANDAY converts a date to a Julian day number — a continuous count of days. Subtract two Julian days and you get the number of days between them. Divide by 365.25 and you have a rough tenure in years. This is how you answer 'who has been here longest?' without eyeballing a list of dates. The result is a floating-point number, so use CAST(... AS INTEGER) when you need whole years for a label.

flowchart LR
  D["#quot;2019-03-11#quot;"] --> S["STRFTIME(#quot;%Y#quot;, hired_on)"] --> Y["2019"]
  D --> J["JULIANDAY(hired_on)"] --> N["2458554"]
  style Y fill:#0e7490,color:#fff

STRFTIME extracts a part; JULIANDAY turns a date into a day count.

Extract year and compute days since hiring.

SELECT name, hired_on,
  STRFTIME('%Y', hired_on) AS hired_year,
  JULIANDAY('now') - JULIANDAY(hired_on) AS days_here
FROM employees;

Building readable reports

The real power appears when you combine string and date functions in one expression. A dashboard rarely wants raw columns; it wants formatted labels. You can build those entirely in SQL by nesting functions and using || to glue the pieces together.

For example, to show 'Chen (5 years)' you compute the Julian-day difference, divide by 365.25, round down with CAST(... AS INTEGER), and concatenate the name and the result. Because every function returns a value, you can nest them as deeply as the report requires. The rule of thumb is to push formatting as close to the database as possible — it reduces the amount of application code you have to write and test, keeps the data layer self-contained, and makes the query reusable across different front-end tools.

flowchart LR
  N["name"] --> B["build label"]
  T["tenure"] --> B
  B --> L["Chen (5 years)"]
  style L fill:#0e7490,color:#fff

String concatenation merges name and computed tenure into one label.

Format a tenure label for each employee.

SELECT name || ' (' ||
  CAST((JULIANDAY('now') - JULIANDAY(hired_on)) / 365.25 AS INTEGER)
  || ' years)' AS tenure_label
FROM employees
ORDER BY hired_on;

Practice: text and time

The exercises ask you to clean names, find hires from a specific year, and build a formatted report column. The last exercise is a transfer task: you will need to combine SUBSTR, string concatenation, and a date function in a single expression — something no earlier snippet showed verbatim. Remember that every function returns a value, so you can nest them or place them anywhere a column name would go.

Exercise

Return each employee's name and a column called code that contains the first three letters of their name in uppercase. Use SUBSTR and UPPER.

SELECT name,
  -- build code here
  AS code
FROM employees;

SELECT name, UPPER(SUBSTR(name, 1, 3)) AS code FROM employees;

Exercise

List the name and hired_on of every employee who was hired in the year 2020. Use a date function to extract the year — do not hard-code a date range with BETWEEN.

SELECT name, hired_on
FROM employees
-- filter by year here
;

SELECT name, hired_on FROM employees WHERE STRFTIME('%Y', hired_on) = '2020';

Exercise

This query is meant to find employees whose role contains the word 'Engineer'. It uses = instead of LIKE and the wildcard syntax is wrong for SQL. Fix it so it returns all engineers, including 'Senior Engineer' and 'Engineering Lead'.

SELECT name, role
FROM employees
WHERE role = '*Engineer*';

SELECT name, role FROM employees WHERE role LIKE '%Engineer%';

Exercise

Create a report that shows name and a column called badge formatted exactly like Nilsen, A. — 2019. To do this you must extract the surname (the word after the space), the initial (first letter of the first name), and the hire year. Use SUBSTR, INSTR, and STRFTIME together.

SELECT name,
  -- build badge here
  AS badge
FROM employees;

SELECT SUBSTR(name, INSTR(name, ' ') + 1) || ', ' || SUBSTR(name, 1, 1) || '. — ' || STRFTIME('%Y', hired_on) AS badge FROM employees;

Exercise

What does LENGTH(' Ada ') return?

Recap

UPPER, LOWER, LENGTH, SUBSTR, and || clean and build text directly in SQL.
SUBSTR uses 1-based indexing and pairs naturally with INSTR to find split points.
STRFTIME extracts parts of a date; JULIANDAY turns a date into a day count for arithmetic.
Date functions expect YYYY-MM-DD; other formats silently misparse.
You can nest functions and concatenate results to build formatted report columns in one query.

Next you will step beyond row-by-row calculations into window functions — computing running totals and partitioned averages without collapsing rows into groups.

String and Date Functions

Text functions: cleaning and slicing

Pattern matching with LIKE

Date functions: treating text like a calendar

Building readable reports

Practice: text and time

Recap

Checkpoint quiz

Go deeper — technical resources