Re: [sqlite] Breaking a string into two

2011-06-15 Thread Michael Stephenson
Well, it's pretty easy to add regex functionality, for example via PCRE. You can add this to your custom build of SQLite, or you can add it to your client application and register a regex function with SQLite after the database is opened, then use it in your queries. I can provide an example if

Re: [sqlite] Breaking a string into two

2011-06-15 Thread BareFeetWare
Hi Simon, Thanks for the reply. > On 16 Jun 2011, at 5:05am, BareFeetWare wrote: > >> I have some source data that includes a "Silo And Region" column of two >> words appended together, such as 'NorthPlains', 'SouthPlains', >> 'NorthSlopes', 'SouthSlopes' etc. I want to split them into two

Re: [sqlite] Breaking a string into two

2011-06-15 Thread Simon Slavin
On 16 Jun 2011, at 5:05am, BareFeetWare wrote: > I have some source data that includes a "Silo And Region" column of two words > appended together, such as 'NorthPlains', 'SouthPlains', 'NorthSlopes', > 'SouthSlopes' etc. I want to split them into two columns. How many different first words

[sqlite] Breaking a string into two

2011-06-15 Thread BareFeetWare
Hi all, I have some source data that includes a "Silo And Region" column of two words appended together, such as 'NorthPlains', 'SouthPlains', 'NorthSlopes', 'SouthSlopes' etc. I want to split them into two columns. How can I do this in SQLite? A regex or offset/position and replace function

Re: [sqlite] Anyway to order query results by "in" list?

2011-06-15 Thread David Garfield
Two implementations: CREATE TABLE t (a int, b int); INSERT INTO t VALUES(1,1); INSERT INTO t VALUES(2,4); INSERT INTO t VALUES(3,9); INSERT INTO t VALUES(4,16); INSERT INTO t VALUES(5,25); INSERT INTO t VALUES(6,36); INSERT INTO t VALUES(7,49); INSERT INTO t VALUES(8,64); INSERT INTO t

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

2011-06-15 Thread Simon Slavin
On 15 Jun 2011, at 10:38pm, Jean-Christophe Deschamps wrote: >> and >> when the callback function is entered, Django claims that the DELETE >> statement has been executed. Is this the callback function called /by/ the DELETE command ? If so, it's called before the DELETE command is

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

2011-06-15 Thread Jean-Christophe Deschamps
Kevin, >I have confirmed that the DELETE does indeed delete the record, but >only after Django's delete callback has completed. If what you say is >true, that DELETEs are proceed immediately, then the only other >explanation is that Django, upon sending of the post_delete signal, >has not

Re: [sqlite] Anyway to order query results by "in" list?

2011-06-15 Thread Simon Slavin
On 15 Jun 2011, at 9:20pm, Michael Stephenson wrote: > Wondering if anyone has a way to execute a query that selects rows based on > a list of rowids and returns the results in the order of the rowids passed > in. Make another table with the rowids in, and use a JOIN. Simon.

[sqlite] Anyway to order query results by "in" list?

2011-06-15 Thread Michael Stephenson
Wondering if anyone has a way to execute a query that selects rows based on a list of rowids and returns the results in the order of the rowids passed in. Thanks, ~Mike ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] how do I select on a field that contains text and digits eg: "chr19:200"

2011-06-15 Thread Simon Slavin
On 15 Jun 2011, at 7:48pm, David Lyon wrote: > chr19:200 > chr19:255492 > chr19:255667 > chr19:255879 Can you rejig the database so that leading zeros are always saved ? In other words, so that the data above becomes > chr19:000200 > chr19:255492 > chr19:255667 > chr19:255879 ? If so, you

Re: [sqlite] how do I select on a field that contains text and digits eg: "chr19:200"

2011-06-15 Thread Luuk
On 15-06-2011 20:48, David Lyon wrote: > > normally if I want to select within a range of numbers I can do something > like this: > > sqlite3 $db "select * from SNPS where $Start >= 1 and $Start <= 10" > .. > how do I do the same select statement where the field are pre-appended with

[sqlite] how do I select on a field that contains text and digits eg: "chr19:200"

2011-06-15 Thread David Lyon
normally if I want to select within a range of numbers I can do something like this: sqlite3 $db "select * from SNPS where $Start >= 1 and $Start <= 10" gives: 1 100 222 1123 1122 etc. how do I do the same select statement where the field are pre-appended with a word and a colon eg

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

2011-06-15 Thread Kevin Yoo
Richard, Thank you for your response. I have confirmed that the DELETE does indeed delete the record, but only after Django's delete callback has completed. If what you say is true, that DELETEs are proceed immediately, then the only other explanation is that Django, upon sending of the

Re: [sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

2011-06-15 Thread Richard Hipp
On Wed, Jun 15, 2011 at 1:39 PM, Sam Roberts wrote: > > It would appear that sqlite3 does not immediately write DELETEs to > disk. I have confirmed this behavior by pausing my Django app > immediately upon entrance into my database hook callback, running > sqlite in

[sqlite] Is there a difference between DELETE and UPDATE/INSERT in terms of syncing to disk?

2011-06-15 Thread Sam Roberts
I'm posting this for one of my coworkers whos email isn't showing up. -- Forwarded message -- From: Kevin Yoo I am writing a Django app with sqlite. I am using Django’s database hook framework to perform certain actions on sqlite’s db file: namely, whenever

[sqlite] trigger efficiency question

2011-06-15 Thread Andy Gibbs
Hi, A quick question: which is better (in terms of speed of execution): CREATE TRIGGER x BEFORE INSERT ON y WHEN expr BEGIN SELECT RAISE(ABORT, 'message'); END; ... or ... CREATE TRIGGER x BEFORE INSERT ON y BEGIN SELECT RAISE(ABORT, 'message') WHERE expr; END; Or are the two, for