Text and Date Queries

Trim and Upper

Cleaning Text

String functions can normalize text before filtering or displaying it.

Program

Play the script to trim names, uppercase them, and filter by a selected minimum length.

min_len
trim_upper.sql
Replay: real traced execution (multi-file project)
CREATE TABLE names (raw TEXT);
INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
WITH params(min_len) AS (VALUES (4)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;

CREATE TABLE names (raw TEXT);
INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
WITH params(min_len) AS (VALUES (3)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;

CREATE TABLE names (raw TEXT);
INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
WITH params(min_len) AS (VALUES (5)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;

  1. tables ← 1 row

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
    values this step1 rowtables

  2. names ← 4 rows

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (4)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step4 rowsnames

  3. result ← 1 row

    2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (4)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
    values this step1 rowtables

  2. names ← 4 rows

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (3)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step4 rowsnames

  3. result ← 4 rows

    2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (3)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');
    values this step1 rowtables

  2. names ← 4 rows

    1CREATE TABLE names (raw TEXT);2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (5)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step4 rowsnames

  3. result ← 1 row

    2INSERT INTO names VALUES (' ada '), ('Lin'), ('  mia'), ('Grace ');3WITH params(min_len) AS (VALUES (5)) SELECT raw, UPPER(TRIM(raw)) AS cleaned FROM names WHERE LENGTH(TRIM(raw)) >= (SELECT min_len FROM params) ORDER BY cleaned;
    values this step1 rowresult
TRIM `TRIM(raw)` removes surrounding spaces.
UPPER `UPPER(...)` converts text to uppercase.
LENGTH `LENGTH(...)` counts characters after trimming.