Re: [sqlite] String manipulation with pure sqlite3?
I think you just need to implement an INSTR(x,y,z) where X is the input string. Y is the search string, and z is the search starting location. Typically negative numbers indicate the end of the string to search backwards. So Substr(t1.col, 0, instr(t1.col, '.', -1) ) || '(' t2.col ||')' || substr(t1.col, instr(t1.col,'.',-1) ) from t1, t2 where t1.id = t2.id Should get you pretty close. See load_extension to get an idea on how to build an extension function such as the instr for your purposes. hth Alexander Skwar <[EMAIL PROTECTED]> wrote: Daniel Önnerby schrieb: > This should be a simple task for any programming language to do once the > results has been retrieved. Yes, of course. But it would be nice, if that could be done on SQL level. > With the current expressions in SQLite I believe there is no way to do > this unless you extend SQLite with your own "string_find_last" or > "replace_last" function. Thanks. That's what I figured out as well :) Thanks a lot for confrming this! Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] String manipulation with pure sqlite3?
Daniel Önnerby schrieb: This should be a simple task for any programming language to do once the results has been retrieved. Yes, of course. But it would be nice, if that could be done on SQL level. With the current expressions in SQLite I believe there is no way to do this unless you extend SQLite with your own "string_find_last" or "replace_last" function. Thanks. That's what I figured out as well :) Thanks a lot for confrming this! Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] String manipulation with pure sqlite3?
This should be a simple task for any programming language to do once the results has been retrieved. With the current expressions in SQLite I believe there is no way to do this unless you extend SQLite with your own "string_find_last" or "replace_last" function. Alexander Skwar wrote: Hello. Suppose I've got tables like this: sqlite> .schema t1 CREATE TABLE t1 (id integer primary key not null, name); sqlite> .schema t2 CREATE TABLE t2 (t1id integer, txt STRING NOT NULL); Filled with: sqlite> select * from t1; 1|foo.bar.boing 2|bumm.krach.klong.schepper 3|just.a.test.entry sqlite> select * from t2; 1|kurz 2|etwas laenger Now I'd like to have a SELECT statement, which would return: 1|foo.bar (kurz).boing 2|bumm.krach.klong (etwas laenger).schepper Ie., before the LAST ".", add what's in t2 but put it in brackets (). It is so, that there are more values in t1, then there are in t2. I only want to get those rows, which are listed in t2. sqlite> select * from t1, t2 where t2.t1id = t1.id; 1|foo.bar.boing|1|kurz 2|bumm.krach.klong.schepper|2|etwas laenger Is this doable in pure sqlite3, or would I need to "massage" the returned data in a programming language? Thanks, Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] String manipulation with pure sqlite3?
Hello. Suppose I've got tables like this: sqlite> .schema t1 CREATE TABLE t1 (id integer primary key not null, name); sqlite> .schema t2 CREATE TABLE t2 (t1id integer, txt STRING NOT NULL); Filled with: sqlite> select * from t1; 1|foo.bar.boing 2|bumm.krach.klong.schepper 3|just.a.test.entry sqlite> select * from t2; 1|kurz 2|etwas laenger Now I'd like to have a SELECT statement, which would return: 1|foo.bar (kurz).boing 2|bumm.krach.klong (etwas laenger).schepper Ie., before the LAST ".", add what's in t2 but put it in brackets (). It is so, that there are more values in t1, then there are in t2. I only want to get those rows, which are listed in t2. sqlite> select * from t1, t2 where t2.t1id = t1.id; 1|foo.bar.boing|1|kurz 2|bumm.krach.klong.schepper|2|etwas laenger Is this doable in pure sqlite3, or would I need to "massage" the returned data in a programming language? Thanks, Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] -