SQLite String Functions

SQLite provides a rich set of string functions for transforming, searching, and formatting text values directly inside SQL queries. You can clean messy data, extract substrings, combine fields, and standardize casing without touching any application code.

Sample Table

CREATE TABLE contacts (
  id        INTEGER PRIMARY KEY,
  full_name TEXT,
  email     TEXT,
  phone     TEXT,
  city      TEXT,
  notes     TEXT
);

INSERT INTO contacts VALUES
(1, '  Alice Smith  ', 'ALICE@COMPANY.COM', '9876543210', 'delhi',   'VIP customer. Prefers email.'),
(2, 'bob jones',       'bob@gmail.com',     '01122334455','mumbai',  'Calls only. No email.'),
(3, 'CAROL WHITE',     'carol@work.net',    NULL,         'Bangalore','New user. Trial plan.');

UPPER() and LOWER() — Change Case

SELECT UPPER('hello');         -- HELLO
SELECT LOWER('WORLD');         -- world
SELECT UPPER('sqlite');        -- SQLITE

-- Standardize city names to title-like format
SELECT full_name,
       UPPER(SUBSTR(city,1,1)) || LOWER(SUBSTR(city,2)) AS city_fixed
FROM contacts;

full_name       │ city_fixed
────────────────┼───────────
  Alice Smith   │ Delhi
bob jones       │ Mumbai
CAROL WHITE     │ Bangalore

TRIM(), LTRIM(), RTRIM() — Remove Whitespace

SELECT TRIM('  hello  ');      -- 'hello'
SELECT LTRIM('  hello  ');     -- 'hello  '  (left only)
SELECT RTRIM('  hello  ');     -- '  hello'  (right only)

-- Clean up names with leading/trailing spaces
SELECT TRIM(full_name) AS clean_name FROM contacts;

clean_name
────────────
Alice Smith
bob jones
CAROL WHITE

-- TRIM can also remove specific characters
SELECT TRIM('***hello***', '*');   -- 'hello'
SELECT LTRIM('000042', '0');       -- '42'

LENGTH() — Count Characters

SELECT LENGTH('SQLite');     -- 6
SELECT LENGTH('');           -- 0
SELECT LENGTH(NULL);         -- NULL

SELECT full_name,
       LENGTH(TRIM(full_name)) AS name_length
FROM contacts;

full_name       │ name_length
────────────────┼────────────
  Alice Smith   │ 11
bob jones       │ 9
CAROL WHITE     │ 11

SUBSTR() — Extract Part of a String

-- SUBSTR(string, start, length)
-- start is 1-based (first character is position 1)

SELECT SUBSTR('SQLite', 1, 3);    -- 'SQL'
SELECT SUBSTR('SQLite', 4);       -- 'ite'  (to end)
SELECT SUBSTR('SQLite', -3);      -- 'ite'  (from end)

-- Extract domain from email
SELECT email,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM contacts;

email                │ domain
─────────────────────┼──────────────
ALICE@COMPANY.COM    │ COMPANY.COM
bob@gmail.com        │ gmail.com
carol@work.net       │ work.net

INSTR() — Find Position of a Substring

-- INSTR(string, search) → position of first match (0 if not found)
SELECT INSTR('hello world', 'world');   -- 7
SELECT INSTR('hello world', 'xyz');     -- 0 (not found)
SELECT INSTR('alice@gmail.com', '@');   -- 6

-- Find position of space to split first/last name
SELECT full_name,
       INSTR(TRIM(full_name), ' ') AS space_pos
FROM contacts;

full_name       │ space_pos
────────────────┼──────────
  Alice Smith   │ 6
bob jones       │ 4
CAROL WHITE     │ 6

Split First and Last Name

-- First name: everything before the first space
-- Last name: everything after the first space
SELECT
  TRIM(full_name) AS full,
  TRIM(SUBSTR(TRIM(full_name), 1,
       INSTR(TRIM(full_name),' ') - 1))     AS first_name,
  TRIM(SUBSTR(TRIM(full_name),
       INSTR(TRIM(full_name),' ') + 1))     AS last_name
FROM contacts;

full         │ first_name │ last_name
─────────────┼────────────┼──────────
Alice Smith  │ Alice      │ Smith
bob jones    │ bob        │ jones
CAROL WHITE  │ CAROL      │ WHITE

REPLACE() — Substitute Text

-- REPLACE(string, find, replacement)
SELECT REPLACE('hello world', 'world', 'SQLite');
-- 'hello SQLite'

-- Remove all spaces
SELECT REPLACE('hello world', ' ', '');
-- 'helloworld'

-- Mask phone numbers
SELECT phone,
       REPLACE(phone,
               SUBSTR(phone, 4, 4),
               'XXXX') AS masked_phone
FROM contacts WHERE phone IS NOT NULL;

phone       │ masked_phone
────────────┼──────────────
9876543210  │ 987XXXX210
01122334455 │ 011XXXX4455

String Concatenation with ||

-- || joins strings together
SELECT 'Hello' || ' ' || 'World';    -- 'Hello World'

-- Build a formatted display name
SELECT UPPER(SUBSTR(TRIM(full_name),1,1)) ||
       LOWER(SUBSTR(TRIM(full_name),2))    AS display_name,
       LOWER(email)                         AS email_clean
FROM contacts;

display_name  │ email_clean
──────────────┼──────────────────
Alice smith   │ alice@company.com
Bob jones     │ bob@gmail.com
Carol white   │ carol@work.net

printf() / format() — Format Strings

-- printf works like C's printf
SELECT printf('%05d', 42);         -- '00042'
SELECT printf('%.2f', 3.14159);    -- '3.14'
SELECT printf('%s (%s)', 'Alice', 'VIP');   -- 'Alice (VIP)'

-- Format price as currency string
SELECT printf('₹%,.2f', 25000.0);  -- '₹25000.00'

-- Pad with leading zeros (e.g. order IDs)
SELECT printf('ORD-%05d', id) AS order_id FROM contacts;

order_id
──────────
ORD-00001
ORD-00002
ORD-00003

LIKE and GLOB for Pattern Matching

-- LIKE (case-insensitive for ASCII)
SELECT full_name FROM contacts WHERE full_name LIKE '%smith%';

-- GLOB (case-sensitive, uses * and ? wildcards)
SELECT email FROM contacts WHERE email GLOB '*@gmail.com';

GLOB patterns:
  *   → any sequence of characters
  ?   → exactly one character
  [abc] → any one of a, b, c

SELECT email FROM contacts WHERE email GLOB '[A-Z]*';
-- Emails starting with uppercase letter: ALICE@COMPANY.COM

COALESCE() for NULL String Handling

-- Replace NULL phone with a default label
SELECT full_name,
       COALESCE(phone, 'No phone on file') AS contact_phone
FROM contacts;

full_name       │ contact_phone
────────────────┼───────────────────
  Alice Smith   │ 9876543210
bob jones       │ 01122334455
CAROL WHITE     │ No phone on file

String Functions Quick Reference

FUNCTION                  DESCRIPTION                   EXAMPLE
──────────────────────────────────────────────────────────────────────────
UPPER(s)                  Uppercase                     UPPER('hi') → 'HI'
LOWER(s)                  Lowercase                     LOWER('HI') → 'hi'
TRIM(s)                   Strip both-side whitespace    TRIM(' hi ') → 'hi'
LTRIM(s)                  Strip left whitespace         LTRIM(' hi') → 'hi'
RTRIM(s)                  Strip right whitespace        RTRIM('hi ') → 'hi'
TRIM(s, chars)            Strip specific characters     TRIM('**hi**','*')→'hi'
LENGTH(s)                 Character count               LENGTH('abc') → 3
SUBSTR(s, pos, len)       Extract substring             SUBSTR('abcde',2,3)→'bcd'
INSTR(s, find)            Position of substring         INSTR('abc','b') → 2
REPLACE(s, find, new)     Replace occurrences           REPLACE('aXb','X','Y')→'aYb'
s1 || s2                  Concatenate strings           'a'||'b' → 'ab'
printf(fmt, ...)          Formatted string              printf('%05d',7)→'00007'
LIKE pattern              Case-insensitive match        name LIKE 'al%'
GLOB pattern              Case-sensitive match          email GLOB '*@*.com'

Practical Data Cleaning Query

-- Clean up the entire contacts table output in one query
SELECT
  printf('CON-%03d', id)                               AS contact_id,
  TRIM(
    UPPER(SUBSTR(TRIM(full_name),1,1)) ||
    LOWER(SUBSTR(TRIM(full_name),2))
  )                                                     AS display_name,
  LOWER(TRIM(email))                                    AS email,
  COALESCE(phone, '—')                                  AS phone,
  UPPER(SUBSTR(city,1,1)) || LOWER(SUBSTR(city,2))      AS city
FROM contacts;

contact_id │ display_name │ email             │ phone       │ city
───────────┼──────────────┼───────────────────┼─────────────┼──────────
CON-001    │ Alice smith  │ alice@company.com  │ 9876543210  │ Delhi
CON-002    │ Bob jones    │ bob@gmail.com      │ 01122334455 │ Mumbai
CON-003    │ Carol white  │ carol@work.net     │ —           │ Bangalore

Key Takeaways

  • TRIM() removes leading and trailing whitespace; essential for cleaning imported data
  • UPPER() and LOWER() standardize text casing for consistent comparisons
  • SUBSTR(s, pos, len) extracts part of a string starting at position pos
  • INSTR(s, find) returns the character position of a substring — 0 if not found
  • REPLACE(s, find, new) swaps every occurrence of a pattern with a new string
  • || concatenates strings; printf() formats them with placeholders
Previous lesson
Back to course
Next lesson

Leave a Comment

Your email address will not be published. Required fields are marked *