Zerethon IDE
Sign in

Text functions and patterns

Join text with ||, upper, length, substr, replace, trim, and match patterns with LIKE and GLOB.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗
main.sql 
SELECT upper(name) AS upper_name,
       length(name) AS len,
       substr(name, 1, 2) AS first2,
       name || ' (' || year || ')' AS label,
       replace(name, 'a', '4') AS replaced
FROM students
WHERE name GLOB 'A*'
ORDER BY name;
setup/schema.sql 
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, year INTEGER);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students (id),
    course_id INTEGER REFERENCES courses (id),
    grade REAL
);

INSERT INTO students VALUES (1, 'Ada', 2), (2, 'Alan', 3), (3, 'Grace', 2), (4, 'Linus', 1);
INSERT INTO courses VALUES (1, 'Algorithms'), (2, 'Databases'), (3, 'Networks');
INSERT INTO enrollments VALUES (1, 1, 9.5), (1, 2, 8.0), (2, 1, 7.5), (3, 2, 9.0), (3, 3, NULL);

Result

upper_namelenfirst2labelreplaced
ADA3AdAda (2)Ad4
ALAN4AlAlan (3)Al4n
Uses the school database in setup/schema.sql (shown below the query): students, courses and enrollments.
  • Join text with || (SQLite also has concat()); + adds numbers.
  • LIKE ignores the case of ASCII letters; GLOB is case-sensitive and uses * and ? like file names.
  • substr(text, start, length) counts from 1.

Watch out: SQLite has no built-in regular-expression function: REGEXP fails with no such function: REGEXP. Use LIKE, GLOB (which supports character classes such as [0-9]) or instr().

Related topics

Official references