SQLite Basics: Clean and Split Text with Core Functions

Imported labels and compact references often need small, inspectable transformations. SQLite’s trim(), instr(), substr(), and replace() functions can perform those operations directly in a SELECT before you decide whether to store the result. Keep the original value visible while checking a cleanup rule.

These scripts require SQLite 3.8.3 or later because the second example uses a common table expression. That is a syntax floor for these examples, not a claim that the text functions were introduced together. Only the listed runtime was executed. SQLite 3.8.3 release notes.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation starts with a fresh in-memory main database and prints pipe-separated rows, showing missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The toy database disappears when each invocation closes its connection. The examples are independent and can be run in any order.

1. Trim selected edge characters without changing internal text

Save as trim-imported-labels.sql:

CREATE TABLE labels (id INTEGER PRIMARY KEY, raw TEXT NOT NULL);
INSERT INTO labels VALUES
    (1, '  Red pen  '),
    (2, '...Pad...'),
    (3, '  Pen  box. ');
SELECT id, quote(trim(raw)), quote(trim(raw, ' .'))
FROM labels ORDER BY id;
SELECT hex(trim(char(9) || 'Box' || char(10))),
       trim(char(9) || 'Box' || char(10), char(9) || char(10));

Run:

python3 run-sql.py trim-imported-labels.sql

Output:

1|'Red pen'|'Red pen'
2|'...Pad...'|'Pad'
3|'Pen  box.'|'Pen  box'
09426F780A|Box

The first cleaned column removes ordinary edge spaces. The second also removes edge periods, while the two spaces inside Pen box remain. The second argument is a set of characters to remove repeatedly from the ends, not a prefix or suffix substring.

The hexadecimal result makes the surviving tab and newline visible without disturbing the terminal layout: 09 and 0A remain when trim() has no custom character set. Supplying those characters explicitly produces Box. This is why a promise to remove all whitespace needs a defined policy rather than an assumption about the default.

2. Split at the first delimiter and preserve missing delimiters

Save as split-tagged-references.sql:

CREATE TABLE references_in (id INTEGER PRIMARY KEY, value TEXT NOT NULL);
INSERT INTO references_in VALUES
    (1, 'ops:release'),
    (2, 'docs:setup:v2'),
    (3, 'plain');
WITH located AS (
    SELECT id, value, instr(value, ':') AS position
    FROM references_in
)
SELECT id,
       CASE WHEN position > 0
            THEN substr(value, 1, position - 1)
            ELSE 'uncategorized' END AS category,
       CASE WHEN position > 0
            THEN substr(value, position + 1)
            ELSE value END AS remainder
FROM located ORDER BY id;
SELECT substr('café-報告', -2), instr('café-報告', '-');

Run:

python3 run-sql.py split-tagged-references.sql

Output:

1|ops|release
2|docs|setup:v2
3|uncategorized|plain
報告|5

The docs row keeps setup:v2 together because instr() locates the first colon. The plain row gets an explicit fallback instead of being accidentally sliced at position zero. Decide separately what leading or trailing delimiters should mean before using this as an import contract.

For text, positions are one-based character positions. A negative substr() start counts from the right, which extracts the final two characters in the last query. These functions do not implement a full parser, and character slicing is not a guarantee about complete user-perceived grapheme clusters.

3. Preview literal replacement before storing it

Save as replace-label-separators.sql:

CREATE TABLE locations (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
INSERT INTO locations VALUES
    (1, 'north_rack_1'), (2, 'south_rack_2'), (3, 'Office');
SELECT id, label, replace(label, '_', ' ') AS proposed
FROM locations ORDER BY id;
UPDATE locations SET label = replace(label, '_', ' ')
WHERE id = 1;
SELECT 'stored', id, label FROM locations ORDER BY id;
SELECT replace('A-a-a', 'a', 'x');

Run:

python3 run-sql.py replace-label-separators.sql

Output:

1|north_rack_1|north rack 1
2|south_rack_2|south rack 2
3|Office|Office
stored|1|north rack 1
stored|2|south_rack_2
stored|3|Office
A-x-x

The preview displays every proposed label without changing a row. The UPDATE then stores the transformation only for id 1; south_rack_2 remains unchanged. This makes the scope of the write easy to inspect rather than assuming a preview has already cleaned the data.

replace() changes every occurrence of the literal search text, not just a suffix, and it is not a regular-expression replacement. The final result retains uppercase A, showing the case-sensitive comparison. For a rule such as changing only a filename extension, test the suffix explicitly instead of replacing that text everywhere.

The core function reference describes trim(), instr(), substr(), and replace(). The examples constrain their inputs to non-null text; decide how missing values should propagate before applying the same expressions to nullable fields. Keep cleanup rules separate from validation of the resulting business identifiers.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py trim-imported-labels.sql split-tagged-references.sql replace-label-separators.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.