Powerful string functions in SQLite

SQLite includes basic text functions like instr, substr and replace (and even trim in later versions), which can get you quite far. But I've always wanted a more complete set, like the one in PostgreSQL, Python or Go.

So I've created a sqlean-text extension that provides dozens of string functions, from slice, contains and count to split, translate and repeat. It also provides Unicode-aware functions for changing text case (upper, lower, title), plus a custom nocase collation.

Note. Unlike other DBMS, adding extensions to SQLite is a breeze. Download a file, run one database command — and you are good to go.

Many functions are Postgres-compatible (i.e. they have the same alias and logic as in PostgreSQL). It can be useful when migrating from SQLite to PostgreSQL or vice versa.

Note that regular expression functions are in the separate regexp extension.

Substrings and slicing • Search and match • Split and join • Trim and pad • Change case • Other modifications • String properties • Installation and usage

Substrings and slicing

text_substring extracts a substring of the given length from the given position:

select text_substring('hello world', 7);
-- world
select text_substring('hello world', 7, 5);
-- world

world
world

text_slice extracts a substring given the start and end positions:

select text_slice('hello world', 7);
-- world
select text_slice('hello world', 7, 12);
-- world
select text_slice('hello world', -5);
-- world
select text_slice('hello world', -5, -2);
-- wor

world
world
world
wor

text_left and text_right extract a substring of the given length from the beginning or end of the string:

select text_left('hello world', 5);
-- hello
select text_left('hello world', -6);
-- hello

select text_right('hello world', 5);
-- world
select text_right('hello world', -6);
-- world

hello
hello
world
world

Search and match

text_index and text_last_index return the first or last index of the given substring in the original string:

select text_index('hello yellow', 'ello');
-- 2
select text_index('hello yellow', 'x');
-- 0

select text_last_index('hello yellow', 'ello');
-- 8
select text_last_index('hello yellow', 'x');
-- 0

text_contains checks if the string contains the given substring:

select text_contains('hello yellow', 'ello');
-- 1
select text_contains('hello yellow', 'x');
-- 0

1
0

text_has_prefix and text_has_suffix check if the string starts or ends with the given substring:

select text_has_prefix('hello yellow', 'hello');
-- 1
select text_has_prefix('hello yellow', 'yellow');
-- 0

select text_has_suffix('hello yellow', 'hello');
-- 0
select text_has_suffix('hello yellow', 'yellow');
-- 1

text_count counts how many times the given substring is contained in the original string.

select text_count('hello yellow', 'ello');
-- 2
select text_count('hello yellow', 'x') = 0;
-- 0

2
0

text_like reports whether a string matches a pattern using the LIKE syntax (Unicode-aware, unlike the built-in LIKE operator):

select text_like('cóm_ está_', 'CÓMO ESTÁS');
-- 1
select text_like('ça%', 'Ça roule');
-- 1

1
1

Split and join

text_split (aka split_part) splits a string by a separator and returns the n-th part:

select text_split('one|two|three', '|', 2);
-- two
select text_split('one|two|three', '|', -1);
-- three
select text_split('one|two|three', ';', 2);
-- (empty string)

two
three

text_concat concatenates strings:

select text_concat('one', 'two', 'three');
-- onetwothree
select text_concat('one', null, 'three');
-- onethree

onetwothree
onethree

text_join joins strings using the separator:

select text_join('|', 'one', 'two');
-- one|two
select text_join('|', 'one', null, 'three');
-- one|three

one|two
one|three

text_repeat concatenates the string to itself a given number of times:

select text_repeat('one', 3);
-- oneoneone

oneoneone

Trim and pad

text_ltrim, text_rtrim and text_trim trim certain characters from the beginning or end of the string:

select text_ltrim('  hello');
-- hello
select text_ltrim('273hello', '123456789');
-- hello

select text_rtrim('hello  ');
-- hello
select text_rtrim('hello273', '123456789');
-- hello

select text_trim('  hello  ');
-- hello
select text_trim('273hello273', '123456789');
-- hello

hello
hello
hello
hello
hello
hello

text_lpad and text_rpad pad the string to the specified length by prepending or appending certain characters:

select text_lpad('hello', 7);
--   hello
select text_lpad('hello', 7, '*');
-- **hello

select text_rpad('hello', 7);
-- hello
select text_rpad('hello', 7, '*');
-- hello**

  hello
**hello
hello
hello**

Change case

text_upper, text_lower and text_title transform a string to upper, lower or title case (Unicode-aware, unlike the built-in upper and lower functions):

select text_upper('cómo estás');
-- CÓMO ESTÁS
select text_lower('CÓMO ESTÁS');
-- cómo estás
select text_title('cómo estás');
-- Cómo Estás

CÓMO ESTÁS
cómo estás
Cómo Estás

The text_nocase collating sequence compares strings without regard to case:

select 1 where 'cómo estás' = 'CÓMO ESTÁS';
-- (null)
select 1 where 'cómo estás' = 'CÓMO ESTÁS' collate text_nocase;
-- 1

Other modifications

text_replace replaces a substring in the original string, but not more than a given number of times:

select text_replace('hello', 'l', '*');
-- he**o
select text_replace('hello', 'l', '*', 1);
-- he*lo

he**o
he*lo

text_translate replaces each string character matching the given set with the corresponding character from the other set:

select text_translate('hello', 'ol', '01');
-- he110
select text_translate('hello', 'ol', '0');
-- he0

he110
he0

text_reverse reverses the order of the characters in the string:

select text_reverse('hello');
-- olleh

olleh

String properties

text_length, text_size and text_bitsize return the number of characters, bytes or bits in the string:

select text_length('𐌀𐌁𐌂');
-- 3
select text_size('𐌀𐌁𐌂');
-- 12
select text_bitsize('𐌀𐌁𐌂');
-- 96

3
12
96

Installation and usage

Download the latest release
Use with SQLite command-line interface:

sqlite> .load ./text
sqlite> select text_reverse('hello');