Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 11:20 PM, starvingpilot wrote: > > > > Puneet Kishor-2 wrote: >> >> >> On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: >> >>> >>> Here's a query that works >>> >>> sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE >>> state like '%@

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: > > Here's a query that works > > sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE > state like '%@ %'",theState]; <--- this yields a result: 0 > > sqlStatement = [NSString stringWithFormat:@"SELECT state, power || '

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 11:04 PM, starvingpilot wrote: > > I actually did have the parenthesis there... I didnt copy and paste the last > one. It was there however and I am still having problems > Well, it is hard to help you unless you tell us exactly what problem you are having. You might

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 11:01 PM, starvingpilot wrote: > > Also, I need to select from a specific state like "CA" Where is that part in > the query? SELECT state, power || ' kW' power FROM ( SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 10:47 PM, starvingpilot wrote: > > Thanks for the quick reply Puneet. However I had a syntax error on App as > well as when I typed that query on my sqlite browser. Here's my syntax > "SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, > 'kW') AS

Re: [sqlite] trim alpha numeric string so it's numeric cast then order

On Apr 18, 2012, at 10:27 PM, starvingpilot wrote: > > Greetings! I am currently working on an iOS app and not too well versed in > SQLite. Here's what I am trying to do. > > I have a table "stations" and the fields "state" populated by US States and > "power" which is populated with strings

Re: [sqlite] auto-incrementing integer in composite primary key

d' and 'created_on' columns, but 'autoincrement' keyword seems to work only with 'primary key' invocation. > > On 04/16/2012 06:27 PM, Mr. Puneet Kishor wrote: >> Given >> >> CREATE TABLE t ( >> id INTEGER NOT NULL, >>

[sqlite] auto-incrementing integer in composite primary key

Given CREATE TABLE t ( id INTEGER NOT NULL, created_on DATETIME DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (id, created_on) ); how can I make just the 'id' column auto-increment? -- Puneet Kishor

Re: [sqlite] FIRST/LAST function

On Feb 2, 2012, at 9:46 AM, Petite Abeille wrote: > > On Feb 2, 2012, at 4:37 PM, Bill McCormick wrote: > >> Does SQLite have FIRST and LAST aggregate function? > > No, sadly, SQLite doesn't support any analytic functions (aka window > function) such as first, last, lead, lag, rank, etc,

Re: [sqlite] Perl script to output to file

On Feb 1, 2012, at 10:20 AM, Bill McCormick wrote: > From the sqlite command line interface, the .output command will output the > result of a query to a file. > > How can I do this from a Perl script? I'm not finding any built-in facility > to do this in the Perl module. > pseudocode

[sqlite] finding pairwise similarity

I have two tables like so CREATE TABLE c (c_no INTEGER PRIMARY KEY, c_name TEXT); CREATE TABLE t (t_no INTEGER PRIMARY KEY, t_name TEXT); CREATE TABLE c_t (c_no INTEGER, t_no INTEGER); Every row in `c` has one or more `t`. I want, in a single SQL, the following source_c,

Re: [sqlite] Database Diagram

On Dec 6, 2011, at 6:45 AM, priya786 wrote: > Hello i want to know how to get the database diagram from sqlite.Please tell > me the solution. If you are on a Mac, SQL Editor is a very nice product for about $80. http://www.malcolmhardie.com/sqleditor/ -- Puneet Kishor

Re: [sqlite] Slow INDEX

On Nov 2, 2011, at 11:31 AM, Fabian wrote: > 2011/11/2 Mr. Puneet Kishor <punk.k...@gmail.com> > >> >> Others will have better answers, but methinks that when you reboot the >> computer, the operating system's caches are flushed out, which slows the >>

Re: [sqlite] Slow INDEX

On Nov 2, 2011, at 11:24 AM, Fabian wrote: > Now if I re-open the database, I can add an additional 10.000 rows very > fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional > 10.000 rows, it takes at least 30 secs, which seems very slow, if I can add > the first 1 million in

Re: [sqlite] Tables as ASCII - is it possible?

On Oct 22, 2011, at 11:34 PM, Paul Linehan wrote: > If I could go with > a scripting language, it would be Python - vastly superior IMHO > to Perl - YMMV. Yup. My mileage does vary. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Tables as ASCII - is it possible?

On Oct 22, 2011, at 11:06 PM, Paul Linehan wrote: > Hi all, > > Is there a way of storing SQLite data (tables) as ASCII text rather > than as binary data? > > I want to be able to run scripts against my data as well as use SQLite. > .. Are you suggesting that you want to treat text data as a

Re: [sqlite] Error Message near ".": syntax error

On Oct 8, 2011, at 8:28 PM, James Brison wrote: > I am receiving the following error message when running a query against a > sqlite db: > > Error Message near ".": syntax error > > What does this mean? and how do I debug it? I'm new to sqlite and don't > understand the 'near'. Is it

Re: [sqlite] how to disable a trigger

On Oct 2, 2011, at 10:29 PM, Igor Tandetnik wrote: > Sam Carleton wrote: >> Is there any way to "disable" a trigger in sqlite? > > If you have control over the trigger's definition, you could do something > like this: > > create trigger MyTrigger on ... > when

Re: [sqlite] how to disable a trigger

On Oct 2, 2011, at 10:12 PM, Sam Carleton wrote: > Ok, how do I list what a trigger is so that I can add it back once I want to > "reactive" it? You are looking to temporarily deactivate a TRIGGER, but there is no such mechanism. You could simply copy the code for the TRIGGER, then DROP it,

Re: [sqlite] speeding up FTS4

Well, defeated by FTS4 for now, I will try the following approach -- 1. drop the fts tables and rebuild them and test. 2. if the above doesn't work, then either migrate the data to Postgres and use its fts, or implement e-Swish or httpdig for full text search. On Sep 28, 2011, at 4:35 PM,

Re: [sqlite] new column

On Sep 25, 2011, at 10:53 PM, 守株待兔 wrote: > there are tow column x1,x2 in table t ,all real > alter table t add column c1 real; > select x1+x2 as c1 from t; > there ara output in my screen,but in the database, > c1 column has no value,how can i make the value of c1 column =x1+x2? UPDATE

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 1:06 PM, Simon Slavin wrote: > > On 17 Sep 2011, at 6:42pm, Petite Abeille wrote: > >> On Sep 17, 2011, at 7:33 PM, Simon Slavin wrote: >> >>> As was clear from my post, I was referring to SQL standards. What various >>> implementation vendors choose to do is up to them.

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 10:33 AM, Simon Slavin wrote: > > On 17 Sep 2011, at 4:29pm, Mr. Puneet Kishor wrote: > >> The multiline INSERT capability may not be a SQL standard, but it is not >> only highly convenient, it is also supported by Pg, the ostensible role >

Re: [sqlite] INSERT syntax missing key SQL syntax

On Sep 17, 2011, at 10:11 AM, Igor Tandetnik wrote: > Jim Michaels > > wrote: >> INSERT is supposed to handle multiple rows for VALUES. > > Supposed by whom? What is the basis for this claim? The multiline INSERT

Re: [sqlite] help with a complicated join of two tables

On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > Mr. Puneet Kishor <punk.k...@gmail.com> wrote: >>>> The table geo can also have rows with min_age = max_age. I want a result >>>> set with geo.id, min_age, max_age, age_bottom, age_top, >>>> name,

Re: [sqlite] help with a complicated join of two tables

On Sep 11, 2011, at 9:58 PM, Igor Tandetnik wrote: > Mr. Puneet Kishor <punk.k...@gmail.com> wrote: >> geo table: 39K rows >> id max_age min_age >> --- --- >> 1 Holocene Holocene >> 5 Cambrian Silurian >> 12 Cambrian Ordovician >&g

[sqlite] help with a complicated join of two tables

Apologies in advance for a terrible subject line -- I didn't know quite how to phrase it better. I have the following two tables (with sample data) CREATE TABLE geo ( id INTEGER PRIMARY KEY, max_age TEXT, min_age TEXT ); geo table: 39K rows id max_age

Re: [sqlite] PRAGMA user_version

On Sep 3, 2011, at 8:21 PM, Walter wrote: > > On 9/2/2011 11:02 PM, Walter wrote: > >> > Is there any way to get the user_version from an Attached database >> > PRAGMA attachedName.user_version; > > -- Igor Tandetnik Thank you Igor I had the database name but did not think of > the the

[sqlite] speeding up an fts query

I have the following schema (slightly simplified for this post) CREATE TABLE projects ( project_id INTEGER PRIMARY KEY, project_start DATETIME ); CREATE TABLE feeds ( feed_id INTEGER PRIMARY KEY, feed_uri TEXT, project_id INTEGER ); CREATE TABLE feed_history (

Re: [sqlite] Is it okay to use SQLite + PHP for Library Software?

On Aug 16, 2011, at 1:23 AM, Darren Duncan wrote: > Raouf Athar wrote: >> I have to develop a Library Management System using *PHP* for a medium sized >> college library. The library has about 5,000 members and 50,000 books. On an >> average, about 500 members will look for books and will be

Re: [sqlite] Will SQLite supports UnQL?

On Jul 31, 2011, at 6:04 AM, Roger Binns wrote: > A different JSON database server product used 4GB and 6 > minutes and I never bothered optimising for it. Perhaps more shocking is > that the other product would usually answer unindexed queries faster than > CouchDB did with indices, mostly

Re: [sqlite] Simple schema design help

On Jun 29, 2011, at 10:35 AM, Ian Hardingham wrote: > Hey guys. > > I have an existing table, the matchTable, where each entry holds a lot > of information about a "match". > > I am adding a tournament system, and each match will either be in a > tournament or not in a tournament. > >

Re: [sqlite] Compute percentage?

On Jun 29, 2011, at 6:53 AM, Roger Andersson wrote: > On 06/29/11 12:34 PM, Gilles Ganault wrote: >> Thanks, that worked: >> SELECT COUNT(*) FROM people; >> 400599 >> >> SELECT COUNT(*) FROM people WHERE zip="12345"; >> 12521 >> >> SELECT (COUNT(rowid)*100)/(SELECT COUNT(*) FROM people) FROM

Re: [sqlite] Selecting indexes to use & NOT INDEXED

On Jun 27, 2011, at 11:18 AM, Mohit Sindhwani wrote: > select * FROM Objects, Objects_Index > WHERE Objects.id = Objects_Index.id > AND minx <= 668632 + 250 AND maxx >= 668632 - 250 > AND miny <= 1518661 + 250 AND maxy >= 1518661 - 250 > AND CAT=25; > > Doing an explain query plan

Re: [sqlite] ISNULL in sqlite

On Jun 25, 2011, at 3:33 PM, logan...@gmail.com wrote: > Hello, > > How do I check for a null or empty string in SQLite. SQL server has ISNULL > but it doesn't seem to be supported in SQLite. ifnull() and nullif() [http://www.sqlite.org/lang_corefunc.html]

Re: [sqlite] Indexes on columns

On Jun 24, 2011, at 1:58 PM, logan...@gmail.com wrote: > Sorry, but seems like I'm missing something here. > > From my understanding it looks like for Integer ID columns that are PK > SQLite doesn't generate any indexes. Is this true? > No, what you think is not true. SQLite does generate an

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

On Jun 23, 2011, at 10:49 AM, Jean-Denis Muys <jdm...@kleegroup.com> wrote: > > On 23 juin 2011, at 16:22, Mr. Puneet Kishor wrote: > >> >> >> >> On Jun 23, 2011, at 10:18 AM, Stephan Beal <sgb...@googlemail.com> wrote: >> >>>

Re: [sqlite] lol: sqlite3 db smaller than the sum of its contents...

On Jun 23, 2011, at 10:18 AM, Stephan Beal wrote: > Hi, all! > > Today i saw a curious thing: i store 440kb of wiki files in an sqlite3 db > and the db file is only 400kb. > > HTF can that possibly be? > > After poking around i found that the wiki files actually

Re: [sqlite] Help with CASE WHEN

On Jun 19, 2011, at 10:04 PM, jose isaias cabrera wrote: > "Mr. Puneet Kishor" wrote... >> >> On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote: >> >>> >>> "Nico Williams" wrote... >>> >>>> On

Re: [sqlite] Help with CASE WHEN

On Jun 19, 2011, at 5:12 PM, jose isaias cabrera wrote: > > "Nico Williams" wrote... > >> On Fri, Jun 17, 2011 at 5:05 PM, Mr. Puneet Kishor <punk.k...@gmail.com> >> wrote: >>> The above is not SQL. You can't have a SQL statement begin with CASE.

Re: [sqlite] Help with CASE WHEN

On Jun 17, 2011, at 4:56 PM, jose isaias cabrera wrote: > CASE (SELECT round(sum(Xtra8),2) FROM LSOpenJobs > WHERE subProjID = 9144 AND lang = 'ES-LA' > AND PSubClass != 'Portal-Fee') > > WHEN < 5000 THEN >UPDATE LSOpenJobs SET ProjFund = (SELECT

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote: > Mr. Puneet Kishor wrote: >> On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: >>> MySQL should be avoided like the plague. >> why? >> This is a long standing (un)conventional wisdom to which I too have h

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: > MySQL should be avoided like the plague. why? This is a long standing (un)conventional wisdom to which I too have hewed. Now, it so happens, I will be starting work on a project that uses MySQL exclusively, and has done so for years. They

Re: [sqlite] alter table add column

On May 31, 2011, at 10:11 AM, Fabio Spadaro wrote: > "Alter table add column" command drop data from table. > Can you keep the data or should I store the data before the alter and then put > them in the table? ALTER TABLE ADD COLUMN does not drop data from the table.

[sqlite] using a view for fts

Consider CREATE VIRTUAL TABLE fts_text USING fts4 (id, content); .. populate the above table, then .. SELECT rowid, Snippet(fts_text) FROM fts_text WHERE fts_text MATCH ?; If I try to do something like CREATE VIEW v_find AS SELECT rowid, Snippet(fts_text) content FROM fts_text; SELECT

[sqlite] compartmentalizing FTS4 searches

My program stores a bunch of text in an FTS4 table and makes it available for search. The wrinkle is, there are conceptually different projects for which the search has to be compartmentalized. CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT); CREATE TABLE documents

Re: [sqlite] time in AM/PM?

On May 15, 2011, at 9:24 PM, Igor Tandetnik wrote: > Mr. Puneet Kishor <punk.k...@gmail.com> wrote: >> I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' >> instead of '10:33' or '13:27'. I don't see any >> formatting options to return the tim

Re: [sqlite] Need to be able to rename attributes

On May 15, 2011, at 10:49 PM, romtek wrote: > Let's not make this issue into something that it's > not. Let's not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] time in AM/PM?

I am trying to get time stamps to be reported as '10:33 AM' and '1:27 PM' instead of '10:33' or '13:27'. I don't see any formatting options to return the time in 12-hour format with AM/PM suffixed. Am I missing something, or do I have to roll my own? Puneet.

[sqlite] FTS4 ranking function in Perl

Has anyone implemented the search result ranking function in Perl? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

On May 15, 2011, at 5:05 PM, romtek wrote: > On Sun, May 15, 2011 at 4:39 PM, Simon Slavin wrote: > >> >> On 15 May 2011, at 10:33pm, romtek wrote: >> >>> So, I am asking developers of SQLite to make it easy for tool developers >> to >>> offer the ability to rename

[sqlite] selecting unique list of latest timestamps

I have a bunch of uris stored in a table CREATE TABLE uris ( uri_id INTEGER PRIMARY KEY, uri TEXT ); uri_id uri -- -- 1 http://foo.com 2

Re: [sqlite] Dynamic SQL for SQLite?

On May 11, 2011, at 7:37 PM, John wrote: > Igor, > What you are suggesting will not work. You can only select values not > columns using case. > > > select case strftime('%w', 'now') > when 0 then sunday_value > when 1 then monday_value > ... > else saturday_value >

Re: [sqlite] Licensing and copyright info?

On May 10, 2011, at 1:59 PM, Don Ireland wrote: > I am writing an app and plan to embed SQLite in my app as a means to store > the data. > > What licensing/copyright statements do I need to make RE SQLite? Nothing. sqlite, the program, is in the Public Domain. SQLite, the term, is

Re: [sqlite] Determining how many columns were returned in a query

On May 8, 2011, at 11:46 AM, Sam Carleton wrote: > On May 8, 2011, at 11:09 AM, Jean-Christophe Deschamps > wrote: > >> >>> How does one go about finding out how many rows a query returns? >> >> This is the number of time sqlite3_step can be called successfully >> until

Re: [sqlite] Need help understanding how to post to this list

On May 3, 2011, at 6:04 PM, Rolf Marsh wrote: > Hello.. I just joined today and can't seem to figure out how to start a > new thread... Can someone please enlighten me? You just did. Just post a question with the subject line indicating clearly what is bothering you and take a seat. The

Re: [sqlite] How to Use an Apostrophe in a Text Field?

On Apr 17, 2011, at 5:54 PM, Alan Holbrook wrote: > I'm using SQLite with VBE2008. I've defined a table with a number of text > fields in it. If the information I want to write to the database contains > an embedded apostrophe, the program throws an error. That

Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?

On Apr 4, 2011, at 7:59 AM, Colin Cuthbert wrote: > > > >> From: punk.k...@gmail.com >> Date: Sun, 3 Apr 2011 07:52:42 -0500 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Question:how to insert row with multiple values from >> same field of different rows of another table? >> >>

Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?

On Apr 3, 2011, at 7:50 AM, Luuk wrote: > On 03-04-2011 14:43, Colin Cuthbert wrote: >> First time I've used this (or any!) mailing list, so sorry if I've done >> something wrong. >> >> Pretty sure my question (in the subect) is phrased badly but it's the best I >> could do! >> >> create

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

On Mar 31, 2011, at 2:28 PM, Doug Currie wrote: > On Mar 31, 2011, at 2:27 PM, Mike Rychener wrote: > >> I have tried the latest Explorer and it gets a syntax error on STDEV. >> However, that function works in Eclipse just fine, to take the standard >> deviation of a column (like min, max,

Re: [sqlite] using sqlite3_get_table --additional info...

On Mar 31, 2011, at 9:30 AM, john darnell wrote: > BTW, if there is a better way to get a row count without using > sqlite3_get_table() that would also work. maybe I am missing something, but what is wrong with "SELECT Count(*) FROM table"? > >

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote: > Hi there, > > I'm trying this for quite some time... and I'm totally stuck. > > I have the following table: > > CREATE TABLE example( > date integer primary key not null, > text text, > ctime TIMESTAMP, > mtime TIMESTAMP); > >