Re: [sqlite] comma-separated string data
Hi, Say the scenario is column value = [1,2,33,45,66] u want to compare value x with the column and retrieve data then use the condition, value = 'x' OR value LIKE 'x,%' OR value LIKE '%,x,%' OR value LIKE '%,x' it should work in most of the cases (y) Thanks, Supriya -- View this message in context: http://sqlite.1065341.n5.nabble.com/comma-separated-string-data-tp74926p76757.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Hi, Say the scenario is column value = [1,2,33,45,66] u want to compare value x with the column and retrieve data then use the condition, value = 'x' OR value LIKE 'x,%' OR value LIKE '%,x,%' OR value LIKE '%,x' it should work in most of the cases (y) Thanks, Supriya -- View this message in context: http://sqlite.1065341.n5.nabble.com/comma-separated-string-data-tp74926p76758.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Mon, Apr 7, 2014 at 5:28 PM, Dominique Devienne wrote: > So the only piece > that's missing is an official way to use vtables "on the fly", and > pass in to its xFilter method the value from the left-correlated value > for each joined left value, so the resulting cursor can "iterate" the > right values. FWIW. --DD Did you mean making joins a special recognizable case for virtual table implementations or something else? I also read different info about Table after your post, never used it or know about them before. But it seemed that they flourish primarily thanks to stored functions. I suppose we can't expect something like this in sqlite any time soon, nevertheless probably some small steps possible. For example, if there is already general and aggregate user functions, then some kind of enumeration one might be possible. In this case support for Table() might be added as a syntax compatibility coating or extra constraint for sqlite to determine the context of the function reference. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Hello, thus, good, incident closed, we've seen worse; I guess, the misunderstanding was triggered by not following up and well on the lipstick 8-p Best. On Mon, Apr 7, 2014 at 4:27 PM, RSmith wrote: > On 2014/04/08 01:02, David Simmons wrote: > >> Why are these people allowed to use this discussion board? >> >> Using SQLite on a critical corporation application I find that by reading >> the material provided it >> is handling terabyte databases with remarkable performance. SQLite does >> not have the >> cost associated with one like Oracle and does not require a full time DBA >> to keep >> things running at mediocre speeds like Oracle or MS SQLServer. Grow up, >> read the material >> supplied, IMPROVE or GAIN programming skills before becoming a critic. >> > > Let me be the first to apologise for whatever has offended you. Sometimes > like-minded individuals in groups might share a common view and sometimes a > jibe or two arise from it. There was no bad intent, but probably this is > not the place for silly remarks and some restraint might go a long way, etc. > > I am however very perplexed by the rest of your note, maybe you are > confused or did not follow the discussion or maybe misunderstood the > content? SQLite was never under fire, quite the contrary, an actual problem > was solved right in this thread via the virtues of SQLite. There were some > notions as to the illogical paradigm some developers favour towards > painting over sad DB designs rather than fixing it, which was highlighted > with a silly analogy or two, but in no way to offend anyone or in any way > pertaining to the utility of SQLite itself. Nobody was a critic with > regards to SQLite. > > If I have misunderstood you, feel free to correct me please, but most of > all, please have a lovely day. > > > > > > > > > > > > > ___ > 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
Re: [sqlite] comma-separated string data
On 2014/04/08 01:02, David Simmons wrote: Why are these people allowed to use this discussion board? Using SQLite on a critical corporation application I find that by reading the material provided it is handling terabyte databases with remarkable performance. SQLite does not have the cost associated with one like Oracle and does not require a full time DBA to keep things running at mediocre speeds like Oracle or MS SQLServer. Grow up, read the material supplied, IMPROVE or GAIN programming skills before becoming a critic. Let me be the first to apologise for whatever has offended you. Sometimes like-minded individuals in groups might share a common view and sometimes a jibe or two arise from it. There was no bad intent, but probably this is not the place for silly remarks and some restraint might go a long way, etc. I am however very perplexed by the rest of your note, maybe you are confused or did not follow the discussion or maybe misunderstood the content? SQLite was never under fire, quite the contrary, an actual problem was solved right in this thread via the virtues of SQLite. There were some notions as to the illogical paradigm some developers favour towards painting over sad DB designs rather than fixing it, which was highlighted with a silly analogy or two, but in no way to offend anyone or in any way pertaining to the utility of SQLite itself. Nobody was a critic with regards to SQLite. If I have misunderstood you, feel free to correct me please, but most of all, please have a lovely day. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Apr 8, 2014, at 1:02 AM, David Simmons wrote: > Why are these people allowed to use this discussion board? Hmmm? What we've got here is failure to communicate perhaps. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Why are these people allowed to use this discussion board? Using SQLite on a critical corporation application I find that by reading the material provided it is handling terabyte databases with remarkable performance. SQLite does not have the cost associated with one like Oracle and does not require a full time DBA to keep things running at mediocre speeds like Oracle or MS SQLServer. Grow up, read the material supplied, IMPROVE or GAIN programming skills before becoming a critic. On Apr 7, 2014, at 3:39 PM, mm.w <0xcafef...@gmail.com> wrote: > "But if the Customer can't tell the difference, does that make you a good > pimp?" > > Hello, > > you just don't get it then you don't get it, that's it. > > Best Regards > > > > On Mon, Apr 7, 2014 at 12:09 PM, RSmith wrote: > >> >> On 2014/04/07 20:57, Petite Abeille wrote: >> >>> Lipstick Driven Design: “You can put lipstick on a pig, but it’s still a >>> pig.” >>> >> >> But if the Customer can't tell the difference, does that make you a good >> pimp? >> >> >> >> ___ >> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
"But if the Customer can't tell the difference, does that make you a good pimp?" Hello, you just don't get it then you don't get it, that's it. Best Regards On Mon, Apr 7, 2014 at 12:09 PM, RSmith wrote: > > On 2014/04/07 20:57, Petite Abeille wrote: > >> Lipstick Driven Design: “You can put lipstick on a pig, but it’s still a >> pig.” >> > > But if the Customer can't tell the difference, does that make you a good > pimp? > > > > ___ > 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
Re: [sqlite] comma-separated string data
On 2014/04/07 20:57, Petite Abeille wrote: Lipstick Driven Design: “You can put lipstick on a pig, but it’s still a pig.” But if the Customer can't tell the difference, does that make you a good pimp? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Apr 7, 2014, at 8:48 PM, mm.w <0xcafef...@gmail.com> wrote: > Dear Petite Abeille, you may repeat it 1 times, they don't listen, they > prefer adding to the previous mistake instead of fixing the origin (hiding > behind falsehood constraints, like it is way it is...) until it will fall > apart with unsolvable issues and developer-made-bugs, surely that's the way > to kill a product even the best selling one. Lipstick Driven Design: “You can put lipstick on a pig, but it’s still a pig.” ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Hello, Dear Petite Abeille, you may repeat it 1 times, they don't listen, they prefer adding to the previous mistake instead of fixing the origin (hiding behind falsehood constraints, like it is way it is...) until it will fall apart with unsolvable issues and developer-made-bugs, surely that's the way to kill a product even the best selling one. Best. On Mon, Apr 7, 2014 at 11:24 AM, Petite Abeille wrote: > > On Apr 7, 2014, at 3:28 PM, Dominique Devienne > wrote: > > > For those interested, here's an article along the same lines that > > better demonstrate what I mean by the above: > > > > > http://technology.amis.nl/2013/06/26/oracle-database-12c-joining-and-outer-joining-with-collections/ > > Aha! That’s what that mysterious CROSS/OUTER APPLY is all about. Thanks > for the link :) > > > The new Oracle 12c join syntax is basically just syntax sugar hiding > > the TABLE operator and its implicit COLUMN_VALUE column. > > Well, table( … ) can apply to records (e.g. pipelined function) with fully > named attributes. > > So, really, we are saying this is rather high cholesterol for > outer/full/cross join table( pipeline( parameter, ... ) )? Is it really > worthwhile a full blown new keyword/concept? Doubtful. > > Anyway… back to SQLite :) > > As James K. Lowden kindly, and repetitively, pointed out: > > http://www.schemamania.org/sql/#lists > > Perhaps worthwhile quoting a few words: > > "Questions are frequently asked about table designs that are hopelessly > wrong. The solution to the question is not to write the query, but to > re-write the table, after which the query will practically write itself. > > Perhaps the most egregious example is a column whose value is a list or, > in SQL terms, a repeating group. The elements in the list are perhaps > comma-separated, and some poor schlep has the task of selecting or joining > on the the nth element in the list.” > > Don’t be *that* schlep. > > N.B. There is no glory in beautifully solving a hopelessly wrong problem. > > > ___ > 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
Re: [sqlite] comma-separated string data
On Apr 7, 2014, at 3:28 PM, Dominique Devienne wrote: > For those interested, here's an article along the same lines that > better demonstrate what I mean by the above: > > http://technology.amis.nl/2013/06/26/oracle-database-12c-joining-and-outer-joining-with-collections/ Aha! That’s what that mysterious CROSS/OUTER APPLY is all about. Thanks for the link :) > The new Oracle 12c join syntax is basically just syntax sugar hiding > the TABLE operator and its implicit COLUMN_VALUE column. Well, table( … ) can apply to records (e.g. pipelined function) with fully named attributes. So, really, we are saying this is rather high cholesterol for outer/full/cross join table( pipeline( parameter, ... ) )? Is it really worthwhile a full blown new keyword/concept? Doubtful. Anyway… back to SQLite :) As James K. Lowden kindly, and repetitively, pointed out: http://www.schemamania.org/sql/#lists Perhaps worthwhile quoting a few words: "Questions are frequently asked about table designs that are hopelessly wrong. The solution to the question is not to write the query, but to re-write the table, after which the query will practically write itself. Perhaps the most egregious example is a column whose value is a list or, in SQL terms, a repeating group. The elements in the list are perhaps comma-separated, and some poor schlep has the task of selecting or joining on the the nth element in the list.” Don’t be *that* schlep. N.B. There is no glory in beautifully solving a hopelessly wrong problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Sun, Apr 6, 2014 at 8:15 PM, Dominique Devienne wrote: > [...]. I'd much prefer a cleaner Oracle-like TABLE() > operator transforming the result array of a table-function operating > on correlated values from a join as an intermediate result-set, i.e. > > select t.key, csv.COLUMN_VALUE from table t TABLE(scvsplit(t.csvfield)) csv For those interested, here's an article along the same lines that better demonstrate what I mean by the above: http://technology.amis.nl/2013/06/26/oracle-database-12c-joining-and-outer-joining-with-collections/ The new Oracle 12c join syntax is basically just syntax sugar hiding the TABLE operator and its implicit COLUMN_VALUE column. Obviously SQLite does not have collection types, nor a TABLE operator, but it does have virtual tables, which are very similar to collection-returning functions in a way, and already "abused" by Max (I say that in a nice way) to the same effect. So the only piece that's missing is an official way to use vtables "on the fly", and pass in to its xFilter method the value from the left-correlated value for each joined left value, so the resulting cursor can "iterate" the right values. FWIW. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Sun, Apr 6, 2014 at 10:23 PM, Dominique Devienne wrote: > > If the answer to either question above is true, then a specialized > vtable would be both more convenient and faster, no? > Hmm... If logical peculiarity of vtable approach (when where-constrained queries might be larger than full-scan one) is acceptable by sqlite (mentioned in my other post), then where expression might serve as parameters so a possible hybrid might be possible (also inspired by the recent discussion of creating user functions on the fly). For example, a virtual table that accepts a Select statement might look like CREATE VIRTUAL TABLE vcommalist USING QueryVirtualizer('WITH RECURSIVE :commalist ') And the actual query using it might look like SELECT * FROM vcommalist WHERE commalist='1,2,3,4,5' This one served more like shortcut, but probably a more broad version is possible when the parameter to virtual table is a print formatted string so one can dynamically customize parameters general parameters can't, i.e., table names, output column names etc. Multiply parameters would be great, but with current state of things the implementation still should use some kind of workaround to ensure correct results so should always return huge estimatedCost in xBestIndex if the constrained arrived doesn't contain at least one required parameter (WHERE clause lacks one) and low one if all parameters are provided. I think that sqlite might as well interpret estimatedCost equal to -1 as a ban to use this index. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Sat, Apr 5, 2014 at 11:46 AM, RSmith wrote: > WITH csvrec(i,l,c,r) AS ( > SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv > UNION ALL > SELECT i, > instr(c,',') AS vLen, > substr(c,instr(c,',')+1) AS vRem, > substr(c,1,instr(c,',')-1) AS vCSV > FROM csvrec > WHERE vLen>0 > ) > SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt > WHERE t.ID=rt.i AND rt.r<>'' > ORDER BY t.ID > LIMIT 100 Very interesting. Thanks for sharing that. But can this CTE be turned into a view? Or does one need to retype the whole "algorithm" every time one needs "join" on the "virtual" unrolled CSV field table? And assuming such a "CTE view" can de defined, what if one selects from the "CTE view" with a WHERE clause, to get only the CSV fields of a single row of scvrec, would that prevent the whole "tmpcsv" result-set for every row of csvrec to be generated? If the answer to either question above is true, then a specialized vtable would be both more convenient and faster, no? Your CTE has the great benefit to work out of the box though, unlike a vtable, so it's a great example nonetheless. Thanks again for that. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Sun, Apr 6, 2014 at 6:13 PM, Hick Gunter wrote: > The vtable split method will happily accept a field from a join as in > > Select t.key,c.value from table t cross join cmlist on c.commalist=t.field; Thanks. Given Max's other post, I now understand that, although I'll have to code it myself to really see what's going on. Unless someone can point me to publicly available code implementing this (no longer working) trick? But that other post from Max also says this "trick" no longer works since 3.8.0, and may be relying on undocumented (and thus subject to change) behavior. I'd much prefer a cleaner Oracle-like TABLE() operator transforming the result array of a table-function operating on correlated values from a join as an intermediate result-set, i.e. select t.key, csv.COLUMN_VALUE from table t TABLE(scvsplit(t.csvfield)) csv > Virtual tables don't declare virtual indices; they return an index number and > an index string from their BestIndex method. You're nitpicking on semantic here IMHO. When the xBestIndex impl fills in information about the cost of the various accesses SQLite present it, it is in effect "declaring" virtual indices, at least I think about it that way myself. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
The vtable split method will happily accept a field from a join as in Select t.key,c.value from table t cross join cmlist on c.commalist=t.field; Virtual tables don't declare virtual indices; they return an index number and an index string from their BestIndex method. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Samstag, 05. April 2014 10:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] comma-separated string data On Saturday, April 5, 2014, Max Vlasov wrote: > On Fri, Apr 4, 2014 at 10:20 PM, peter korinis > > > wrote: > > A data column in a link table contains comma-separated string data, > > where > > > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to > > extract these values and use them in an SQL statement, perhaps a WHERE > > id='66'? > > In similar cases I use my virtual table explained here: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html > Actually the table works more like "function", so only one instance is > required in the db to apply this trick. > [...] > This trick successfully works with joins and everything else. > I don't think it works in this case Max, because your technique relies on the where clause being a literal, whereas here, if I'm reading between the lines correctly, the poster wants the the equivalent of Oracle's TABLE() operator. In this case, a vtable can still help, but one specific to the source table, with only the source table's PK columns plus the one to "un-nest" / parse. Basically xNext behaves like a compound iterator, with the outer iterator scanning the source table (using normal SQL and the SQLite API), and the inner iterator returning the CSV values one at a time of the current outer iterator's value / row. That's basically normalizing on the fly. The vtable should ALSO declare an index on the PK columns to avoid full scans with a where clause or a join. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
CREATE TABLE tmpcsv ( ID INTEGER PRIMARY KEY, colA TEXT, colCSV TEXT ); INSERT INTO tmpcsv (colA, colCSV) VALUES ('foo', '4,66,51,3009,2,678'), ('bar', 'Sputnik,Discovery'), ('baz', '101,I-95,104'); WITH csvrec(i,l,c,r) AS ( SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv UNION ALL SELECT i, instr(c,',') AS vLen, substr(c,instr(c,',')+1) AS vRem, substr(c,1,instr(c,',')-1) AS vCSV FROM csvrec WHERE vLen>0 ) SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt WHERE t.ID=rt.i AND rt.r<>'' ORDER BY t.ID LIMIT 100 Results: IDcolA r ------ 1foo4 1foo66 1foo51 1foo3009 1foo2 1foo678 2barSputnik 2barDiscovery 3baz101 3bazI-95 3baz104 :) (PS: The Limit is not strictly necessary, but advised during testing to avoid accidental eternal loops). On 2014/04/04 23:12, peter korinis wrote: Thanks, Simon. The database was created by a developer as part of a larger project involving a mobile app and portal. the portal is written in which calls the db. so that's where the 'parsing' is done. But unfortunately a SQL alone cannot provide the data in this case. This portal is in production so we're not about to rewrite the code with a db redesign. Does SQL have any string commands, like REGEXP or something else to strip out the commas, and transform the string into multiple discrete values, then use those perhaps in a SQL subquery . or something like that? ___ 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
Re: [sqlite] comma-separated string data
On Saturday, April 5, 2014, Max Vlasov wrote: > On Fri, Apr 4, 2014 at 10:20 PM, peter korinis > > > wrote: > > A data column in a link table contains comma-separated string data, where > > > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > > these values and use them in an SQL statement, perhaps a WHERE id='66'? > > In similar cases I use my virtual table explained here: > http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html > Actually the table works more like "function", so only one instance is > required in the db to apply this trick. > [...] > This trick successfully works with joins and everything else. > I don't think it works in this case Max, because your technique relies on the where clause being a literal, whereas here, if I'm reading between the lines correctly, the poster wants the the equivalent of Oracle's TABLE() operator. In this case, a vtable can still help, but one specific to the source table, with only the source table's PK columns plus the one to "un-nest" / parse. Basically xNext behaves like a compound iterator, with the outer iterator scanning the source table (using normal SQL and the SQLite API), and the inner iterator returning the CSV values one at a time of the current outer iterator's value / row. That's basically normalizing on the fly. The vtable should ALSO declare an index on the PK columns to avoid full scans with a where clause or a join. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Fri, Apr 4, 2014 at 10:20 PM, peter korinis wrote: > A data column in a link table contains comma-separated string data, where > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > these values and use them in an SQL statement, perhaps a WHERE id='66'? > > In similar cases I use my virtual table explained here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg63453.html Actually the table works more like "function", so only one instance is required in the db to apply this trick. Finally the db contains CREATE VIRTUAL TABLE cmlist Using vtcommalist and the query SELECT value FROM cmlist WHERE commalist='45,56,78,125' returns 4 rows "45" "56" "78" "125" And this was a blank db with this virtual table only and the query doesn't reference anything outside while still generating table data. So the data for the virtual table is actually supplied at the time of the query itself from WHERE clause. This trick successfully works with joins and everything else. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On Fri, 4 Apr 2014 14:20:57 -0400 "peter korinis" wrote: > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to > extract these values and use them in an SQL statement, perhaps a > WHERE id='66'? http://www.schemamania.org/sql/#lists HTH, really. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On 4/4/2014 4:12 PM, peter korinis wrote: Does SQL have any string commands, like REGEXP or something else to strip out the commas, and transform the string into multiple discrete values, then use those perhaps in a SQL subquery . or something like that? Do the processing outside of SQLite. Let's say you have an entry "4,66,51,3009,2,678, .", and you want to get the "66" out of there to feed back into another query. Well, just do whatever magic you like to get that entry into your program, then use regexp or character array indexing or Tcl [split $val ,] or whatever to get "66", and finally use that as a parameter in your query. Doing all this inside SQLite will add complexity with no performance benefit. It's not like SQLite is a database server over a slow network link which you want to use as little as possible. It's a library embedded in your application, which means it's on equal footing with all your other application code. Also yes, do normalize your database design. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
Thanks, Simon. The database was created by a developer as part of a larger project involving a mobile app and portal. the portal is written in which calls the db. so that's where the 'parsing' is done. But unfortunately a SQL alone cannot provide the data in this case. This portal is in production so we're not about to rewrite the code with a db redesign. Does SQL have any string commands, like REGEXP or something else to strip out the commas, and transform the string into multiple discrete values, then use those perhaps in a SQL subquery . or something like that? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
On 4 Apr 2014, at 7:20pm, peter korinis wrote: > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > these values and use them in an SQL statement, perhaps a WHERE id='66'? This is not simple, and would lead to slow execution in a live environment. Are you willing, at this stage, to use that column to create a proper TABLE that links the two existing TABLEs, or do you need to keep the data in your database in that format, and use that column in a live environment ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
peter korinis wrote: > A data column in a link table contains comma-separated string data, where > each value represents a value to link to another table. (many-to-many > relationship) Every time you use non-normalized data ... God kills a kitten. > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > these values and use them in an SQL statement, perhaps a WHERE id='66'? The value could appear at the beginning or in the middle or at the end or be the only value: ... id LIKE '66,%' OR id LIKE '%,66,%' OR id LIKE '%,66' OR id = '66' But please, think of the kittens! 😿 Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] comma-separated string data
A data column in a link table contains comma-separated string data, where each value represents a value to link to another table. (many-to-many relationship) How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract these values and use them in an SQL statement, perhaps a WHERE id='66'? Thanks, peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
You need to normalize the database design. -- On Fri, 2014-04-04 at 14:20 -0400, peter korinis wrote: > A data column in a link table contains comma-separated string data, where > each value represents a value to link to another table. (many-to-many > relationship) > > > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > these values and use them in an SQL statement, perhaps a WHERE id='66'? > > > > Thanks, > > peter > > ___ > 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