It's not obvious but this works
CREATE TABLE x (s string); INSERT INTO "x" VALUES('ab:cdef'); INSERT INTO "x" VALUES('ghij:klmn'); sqlite> select ltrim(ltrim(s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),':') from x; ltri ---- cdef klmn Just make sure your char set contains all possible chars left of the : Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Max Vlasov [max.vla...@gmail.com] Sent: Saturday, June 25, 2011 3:43 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Substring question On Sat, Jun 25, 2011 at 9:53 AM, Pete <p...@mollysrevenge.com> wrote: > I need to select a substring of a column that starts 1 character after a > colon in the column and continues to the end of the column. For example, > if > the column contained "abc:xyz" I want the select statement to return only > "xyz" (the characters after the colon). The substr function requires > specific character positions - is there a way to do this? > > There's no such function is sqlite so your currently has two options: - if your strings have limited length you can create a statement with a case case when substr(..., 2, 1)=':' then when substr(..., 3, 1)=':' ... end you even can write a supplemental program when you enter the range for the length and the position of the colon and the one gives you the case statement - write your own (user) function. If you didn't write any before, believe be, it's very easy. If you go this way I recommend keeping compatibility at least with MySql in this case (so either "position" or "locate" with the corresponding parameters). This will give you theoretical possibility to export you statements in the future without a change. I did this for example when I implemented find_in_set Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users