Re: [sqlite] comma-separated string data

[supriya1890]

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

[supriya1890]

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

[mm.w]

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

[Petite Abeille]

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

[David Simmons]

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

[mm.w]

"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

[RSmith]

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

[mm.w]

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

[Petite Abeille]

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

[Dominique Devienne]

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

[Max Vlasov]

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

[Dominique Devienne]

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

[Dominique Devienne]

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

[Hick Gunter]

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 <max.vla...@gmail.com> wrote:

> On Fri, Apr 4, 2014 at 10:20 PM, peter korinis
> <kori...@earthlink.net<javascript:;>>
> 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

[RSmith]

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

[Dominique Devienne]

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

[Max Vlasov]

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

[James K. Lowden]

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

[Clemens Ladisch]

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

[peter korinis]

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

[Robert Hairgrove]

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