The sqlean-text extension provides a rich set of functions for working with text.
Also provides Unicode-aware functions for changing text case (upper, lower, title), plus a custom nocase collation.
Many of the 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.
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
text_substring(str, start [,length])
Extracts a substring of length characters starting at the start position (1-based). By default, extracts all characters from start to the end of the string.
select text_substring('hello world', 7);
-- world
select text_substring('hello world', 7, 5);
-- worldPostgres-compatible (substr), but not aliased as substr to avoid conflicts with the built-in substr SQLite function.
text_slice(str, start [,end])
Extracts a substring from the start position inclusive to the end position non-inclusive (1-based). By default, end is the end of the string.
Both start and end can be negative, in which case they are counted from the end of the string toward the beginning of the string.
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);
-- wortext_left(str, length)
Extracts a substring of length characters from the beginning of the string. For negative length, extracts all but the last |length| characters.
select text_left('hello world', 5);
-- hello
select text_left('hello world', -6);
-- helloPostgres-compatible, aliased as left.
text_right(str, length)
Extracts a substring of length characters from the end of the string. For negative length, extracts all but the first |length| characters.
select text_right('hello world', 5);
-- world
select text_right('hello world', -6);
-- worldPostgres-compatible, aliased as right.
text_index(str, other)
Returns the first index of the other substring in the original string.
select text_index('hello yellow', 'ello');
-- 2
select text_index('hello yellow', 'x');
-- 0Postgres-compatible, aliased as strpos.
text_last_index(str, other)
Returns the last index of the other substring in the original string.
select text_last_index('hello yellow', 'ello');
-- 8
select text_last_index('hello yellow', 'x');
-- 0text_contains(str, other)
Checks if the string contains the other substring.
select text_contains('hello yellow', 'ello');
-- 1
select text_contains('hello yellow', 'x');
-- 0text_has_prefix(str, other)
Checks if the string starts with the other substring.
select text_has_prefix('hello yellow', 'hello');
-- 1
select text_has_prefix('hello yellow', 'yellow');
-- 0Postgres-compatible, aliased as starts_with.
text_has_suffix(str, other)
Checks if the string ends with the other substring.
select text_has_suffix('hello yellow', 'hello');
-- 0
select text_has_suffix('hello yellow', 'yellow');
-- 1text_count(str, other)
Counts how many times the other substring is contained in the original string.
select text_count('hello yellow', 'ello');
-- 2
select text_count('hello yellow', 'x') = 0;
-- 0text_like(pattern, str)
Reports whether a string matches a pattern using the LIKE syntax.
select text_like('cóm_ está_', 'CÓMO ESTÁS');
-- 1
select text_like('ça%', 'Ça roule');
-- 1Not aliased as like to avoid conflicts with the built-in like SQLite function.
text_split(str, sep, n)
Splits a string by a separator and returns the n-th part (counting from one). When n is negative, returns the |n|th-from-last 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)Postgres-compatible, aliased as split_part.
text_concat(str, ...)
Concatenates strings and returns the resulting string. Ignores nulls.
select text_concat('one', 'two', 'three');
-- onetwothree
select text_concat('one', null, 'three');
-- onethreePostgres-compatible, aliased as concat.
text_join(sep, str, ...)
Joins strings using the separator and returns the resulting string. Ignores nulls.
select text_join('|', 'one', 'two');
-- one|two
select text_join('|', 'one', null, 'three');
-- one|threePostgres-compatible, aliased as concat_ws.
text_repeat(str, count)
Concatenates the string to itself a given number of times and returns the resulting string.
select text_repeat('one', 3);
-- oneoneonePostgres-compatible, aliased as repeat.
text_ltrim(str [,chars])
Trims certain characters (spaces by default) from the beginning of the string.
select text_ltrim(' hello');
-- hello
select text_ltrim('273hello', '123456789');
-- helloPostgres-compatible, aliased as ltrim.
text_rtrim(str [,chars])
Trims certain characters (spaces by default) from the end of the string.
select text_rtrim('hello ');
-- hello
select text_rtrim('hello273', '123456789');
-- helloPostgres-compatible, aliased as rtrim.
text_trim(str [,chars])
Trims certain characters (spaces by default) from the beginning and end of the string.
select text_trim(' hello ');
-- hello
select text_trim('273hello273', '123456789');
-- helloPostgres-compatible, aliased as btrim.
text_lpad(str, length [,fill])
Pads the string to the specified length by prepending certain characters (spaces by default).
select text_lpad('hello', 7);
-- hello
select text_lpad('hello', 7, '*');
-- **helloPostgres-compatible, aliased as lpad.
ℹ️ PostgreSQL does not support unicode strings in lpad, while this function does.
text_rpad(str, length [,fill])
Pads the string to the specified length by appending certain characters (spaces by default).
select text_rpad('hello', 7);
-- hello
select text_rpad('hello', 7, '*');
-- hello**Postgres-compatible, aliased as rpad.
ℹ️ PostgreSQL does not support unicode strings in rpad, while this function does.
text_upper(str)
Transforms a string to upper case.
select text_upper('cómo estás');
-- CÓMO ESTÁSNot aliased as upper to avoid conflicts with the built-in upper SQLite function.
text_lower(str)
Transforms a string to lower case.
select text_lower('CÓMO ESTÁS');
-- cómo estásNot aliased as lower to avoid conflicts with the built-in lower SQLite function.
text_title(str)
Transforms a string to title case.
select text_title('cómo estás');
-- Cómo EstásThe 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;
-- 1text_replace(str, old, new [,count])
Replaces old substrings with new substrings in the original string, but not more than count times. By default, replaces all old substrings.
select text_replace('hello', 'l', '*');
-- he**o
select text_replace('hello', 'l', '*', 1);
-- he*loPostgres-compatible (replace), but not aliased as replace to avoid conflicts with the built-in replace SQLite function.
text_translate(str, from, to)
Replaces each string character that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.
select text_translate('hello', 'ol', '01');
-- he110
select text_translate('hello', 'ol', '0');
-- he0Postgres-compatible, aliased as translate.
ℹ️ PostgreSQL does not support unicode strings in translate, while this function does.
text_reverse(str)
Reverses the order of the characters in the string.
select text_reverse('hello');
-- ollehPostgres-compatible, aliased as reverse.
ℹ️ PostgreSQL does not support unicode strings in reverse, while this function does.
text_length(str)
Returns the number of characters in the string.
select text_length('𐌀𐌁𐌂');
-- 3Postgres-compatible, aliased as char_length and character_length.
text_size(str)
Returns the number of bytes in the string.
select text_size('𐌀𐌁𐌂');
-- 12Postgres-compatible, aliased as octet_length.
text_bitsize(str)
Returns the number of bits in the string.
select text_bitsize('one');
-- 24Postgres-compatible, aliased as bit_length.
SQLite command-line interface:
sqlite> .load ./text
sqlite> select text_reverse('hello');
See How to install an extension for usage with IDE, Python, etc.
↓ Download the extension.
⛱ Explore other extensions.
★ Subscribe to stay on top of new features.