Skip to content

Instantly share code, notes, and snippets.

@njamescouk
Created February 24, 2018 16:55
Show Gist options
  • Save njamescouk/415faaccf22691305971c742896dc7b9 to your computer and use it in GitHub Desktop.
Save njamescouk/415faaccf22691305971c742896dc7b9 to your computer and use it in GitHub Desktop.
elucidating sqlite's substr() function
SELECT '/*
results
initial string "abcdefghijklmnopqrst" of length ' || length('abcdefghijklmnopqrst'); -- 20
SELECT '3 letters starting with 6th =' || substr('abcdefghijklmnopqrst', 6, 3); -- fgh
SELECT '
2 ways of selecting 3 letters starting 6th from the end';
SELECT substr('abcdefghijklmnopqrst', -6, 3); -- opq
SELECT substr('abcdefghijklmnopqrst', (SELECT length('abcdefghijklmnopqrst')+1-6), 3); -- opq
SELECT '
2 ways of selecting 3 letters preceding 6th';
SELECT substr('abcdefghijklmnopqrst', 6, -3); -- cde
SELECT substr('abcdefghijklmnopqrst', 6-3, 3); -- cde
SELECT '
2 ways of selecting 3 letters preceding 6th from end';
SELECT substr('abcdefghijklmnopqrst', -6, -3); -- lmn
SELECT substr('abcdefghijklmnopqrst', (SELECT length('abcdefghijklmnopqrst')+1-6)-3, 3); -- lmn
SELECT '
created with sqlite3 < substr.sql >> substr.sql
*/';
/*
results
initial string "abcdefghijklmnopqrst" of length 20
3 letters starting with 6th =fgh
2 ways of selecting 3 letters starting 6th from the end
opq
opq
2 ways of selecting 3 letters preceding 6th
cde
cde
2 ways of selecting 3 letters preceding 6th from end
lmn
lmn
created with sqlite3 < substr.sql >> substr.sql
*/
@njamescouk
Copy link
Author

sqlite (and sql generally for all I know) can do some fancy indexing in the substr() function. this example may clarify matters.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment