elucidating sqlite's substr() function

substr.sql

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
*/

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