Re: [sqlite] Split Function for SQLite?

[Max Vlasov]

On Thu, Aug 25, 2011 at 9:34 AM, Max Vlasov  wrote:
>
> Also theoretically it is possible to use virtual tables for this. So
> when your virtual query implementation accepts list in some way ('23,
> 14, 1, 7, 9') and returns the table when querying
>

I tried to implement something like this and it seems it works (very
easy for anyone familiar with virtual tables). The table itself
doesn't need any init data (and doesn't even contain) and absorbs data
from the query itself

So finally the db/program that wants to use it creates it with something like
  CREATE VIRTUAL TABLE cmlist Using vtcommalist
Table format
  CREATE TABLE [xxx] ([CommaList] TEXT, [Value] TEXT)
And example query is
  SELECT value FROM cmlist WHERE commalist='45,56,78,125'

So we finally got
"45"
"56"
"78"
"125"

Internally my xFilter just stores the commalist from the query and
uses it for First/Next

The only problem with this approach is that rowid makes sense only for
queries mentioned.
So
  SELECT rowid FROM cmlist
can't return valid results

I wonder can it be a problem for complex queries when sqlite decides
itself what to query and maybe relies on the correctness of rowid.

Max Vlasov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Max Vlasov]

On Tue, Aug 23, 2011 at 7:10 AM, Gregory Moore  wrote:
> I need to split up a list of items in a single row so they each have
> their own row.
>

You can read about my trick query solving partly this task with a trick
http://www.mail-archive.com/sqlite-users@sqlite.org/msg55935.html

This will require a user function (GetItemFromSet) and the results are
limited in a sense. You will get ti idea from the following query (the
version modified by Jim Morris)

SELECT Trim(GetItemFromSet(Value, '23, 14, 1, 7, 9')) Item FROM
SELECT B1.B + B2.B + B3.B + B4.B FROM
(SELECT 0 AS B UNION SELECT 1 AS B) AS B1,
(SELECT 0 AS B UNION SELECT 2 AS B) AS B2,
(SELECT 0 AS B UNION SELECT 4 AS B) AS B3,
(SELECT 0 AS B UNION SELECT 8 AS B) AS B4
) WHERE NOT (Item Is Null)

Also theoretically it is possible to use virtual tables for this. So
when your virtual query implementation accepts list in some way ('23,
14, 1, 7, 9') and returns the table when querying

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Michael Stephenson]

If your programming language is C or C++, it's trivial to register a
function with SQLite at runtime that can then be used in your queries.  This
does not require hacking SQLite itself.

As noted, you can't return multiple rows per database row via a function.
That's not how SQL works; it is "set" oriented.

What you could do is write a function to extract the Nth item in a delimited
string, passing the column name and the index of the item you want to
extract, and then you could split out or coalesced string column into
multiple columns like this for example:

select c1, extract(c2,0) as c2_0, extract(c2,1) as c2_1
from mytable

I can imagine a technique that you could possibly use to generate multiple
rows that would have an extract function that keeps internal state and
increments the index of the item to be extracted in a circular way (e.g., 0,
1, 2, 0, 1, 2) and then use a self join on the table to create a cross join
where you get a new row for each combination of c1 and the extracted
component of c2.  But, this is imaginary only and quite a hack and it would
definitely be better to handle this in your program directly.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, August 24, 2011 11:09 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Split Function for SQLite?

> Did you see the code in the link I provided?  It's a function that can be
added to SQL.

Note, it's not SQL. SQL doesn't support "adding functions". SQL is all about
SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE).
That's it. So the link you provided is a function that can be added to T-SQL
which is an internal programming language of MS SQL Server.

SQLite doesn't have its own internal programming language, so it doesn't
support writing stored procedures and functions like the example in the
link. And although SQLite allows to add functions written in C those
functions can't return such datatype as "table". So behavior you want is
impossible to reproduce with SQLite and you have to implement it in your
programming language.
And just to prevent speculations about this: lacking of own programming
language is not an issue for SQLite. Having such language won't have any
benefit because SQLite works differently than any client-server DBMS. Doing
what you want in your programming language will always work faster and is
more convenient to implement.


Pavel


On Wed, Aug 24, 2011 at 10:47 PM, Gregory Moore <thewatchful...@gmail.com>
wrote:
> I don't know know whether it's a SQLite "issue" or not.  Did you see the
code in the link I provided?  It's a function that can be added to SQL. I
just need to find out whether it can be added to SQLite and if so, then how
to add it.  I'd like to know before I try it whether I would run the risk of
messing up my SQLite "installation."   Thanks!
>
> On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote:
>
>> On 8/24/2011 9:05 PM, Gregory Moore wrote:
>>> I'm thinking it's parameters would be the name of a column and a
character to designate where the string wold be separated.  It would return
multiple rows and each row would contain a piece of the string.
>>>
>>> In other word, take a table like this:
>>>
>>> c1 c2
>>> ---
>>> 1  a; b; c
>>>
>>> run a statement like this:
>>>
>>> select c1, split(c2, ';')from t1;
>>>
>>> and get this:
>>>
>>> c1 c2
>>> ---
>>> 1   a
>>> 1   b
>>> 1   c
>>>
>>> Maybe SQLite can do it or not, i don't know.  I've searched using Google
and found code for a few different implementations but they are for other
dialects of SQL.  Here's a link to one such function i found:
>>>
>>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-
>>> break-comma-separated-strings-into-table.aspx
>>>
>>> Would that work with SQLite?
>>>
>>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:
>>>
>>>> On 8/24/2011 9:36 PM, Gregory Moore wrote:
>>>>> Thanks for answering!  Can this not be added as a function?
>>>> What parameters would such a function take, and more interestingly, 
>>>> what would its return value be?
>>>> --
>>>> Igor Tandetnik
>>>>
>>>>
>>>> Gregory Moore
>>>> thewatchful...@gmail.com
>>>>
>> lurker here, but felt compelled, and my apologies in advance, but 
>>
>> Isn't this more a function of whatever language you are using and not 
>> a SQLite issue?
>> ___

Re: [sqlite] Split Function for SQLite?

[Pavel Ivanov]

> Did you see the code in the link I provided?  It's a function that can be 
> added to SQL.

Note, it's not SQL. SQL doesn't support "adding functions". SQL is all
about SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE).
That's it. So the link you provided is a function that can be added to
T-SQL which is an internal programming language of MS SQL Server.

SQLite doesn't have its own internal programming language, so it
doesn't support writing stored procedures and functions like the
example in the link. And although SQLite allows to add functions
written in C those functions can't return such datatype as "table". So
behavior you want is impossible to reproduce with SQLite and you have
to implement it in your programming language.
And just to prevent speculations about this: lacking of own
programming language is not an issue for SQLite. Having such language
won't have any benefit because SQLite works differently than any
client-server DBMS. Doing what you want in your programming language
will always work faster and is more convenient to implement.


Pavel


On Wed, Aug 24, 2011 at 10:47 PM, Gregory Moore
 wrote:
> I don't know know whether it's a SQLite "issue" or not.  Did you see the code 
> in the link I provided?  It's a function that can be added to SQL. I just 
> need to find out whether it can be added to SQLite and if so, then how to add 
> it.  I'd like to know before I try it whether I would run the risk of messing 
> up my SQLite "installation."   Thanks!
>
> On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote:
>
>> On 8/24/2011 9:05 PM, Gregory Moore wrote:
>>> I'm thinking it's parameters would be the name of a column and a character 
>>> to designate where the string wold be separated.  It would return multiple 
>>> rows and each row would contain a piece of the string.
>>>
>>> In other word, take a table like this:
>>>
>>> c1 c2
>>> ---
>>> 1  a; b; c
>>>
>>> run a statement like this:
>>>
>>> select c1, split(c2, ';')from t1;
>>>
>>> and get this:
>>>
>>> c1 c2
>>> ---
>>> 1   a
>>> 1   b
>>> 1   c
>>>
>>> Maybe SQLite can do it or not, i don't know.  I've searched using Google 
>>> and found code for a few different implementations but they are for other 
>>> dialects of SQL.  Here's a link to one such function i found:
>>>
>>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
>>>
>>> Would that work with SQLite?
>>>
>>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:
>>>
 On 8/24/2011 9:36 PM, Gregory Moore wrote:
> Thanks for answering!  Can this not be added as a function?
 What parameters would such a function take, and more interestingly, what
 would its return value be?
 --
 Igor Tandetnik


 Gregory Moore
 thewatchful...@gmail.com

>> lurker here, but felt compelled, and my apologies in advance, but 
>>
>> Isn't this more a function of whatever language you are using and not a
>> SQLite issue?
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> Gregory Moore
> thewatchful...@gmail.com
>
>
>
> ___
> 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] Split Function for SQLite?

[Gregory Moore]

I don't know know whether it's a SQLite "issue" or not.  Did you see the code 
in the link I provided?  It's a function that can be added to SQL. I just need 
to find out whether it can be added to SQLite and if so, then how to add it.  
I'd like to know before I try it whether I would run the risk of messing up my 
SQLite "installation."   Thanks!

On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote:

> On 8/24/2011 9:05 PM, Gregory Moore wrote:
>> I'm thinking it's parameters would be the name of a column and a character 
>> to designate where the string wold be separated.  It would return multiple 
>> rows and each row would contain a piece of the string.
>> 
>> In other word, take a table like this:
>> 
>> c1 c2
>> ---
>> 1  a; b; c
>> 
>> run a statement like this:
>> 
>> select c1, split(c2, ';')from t1;
>> 
>> and get this:
>> 
>> c1 c2
>> ---
>> 1   a
>> 1   b
>> 1   c
>> 
>> Maybe SQLite can do it or not, i don't know.  I've searched using Google and 
>> found code for a few different implementations but they are for other 
>> dialects of SQL.  Here's a link to one such function i found:
>> 
>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
>> 
>> Would that work with SQLite?
>> 
>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:
>> 
>>> On 8/24/2011 9:36 PM, Gregory Moore wrote:
 Thanks for answering!  Can this not be added as a function?
>>> What parameters would such a function take, and more interestingly, what
>>> would its return value be?
>>> -- 
>>> Igor Tandetnik
>>> 
>>> 
>>> Gregory Moore
>>> thewatchful...@gmail.com
>>> 
> lurker here, but felt compelled, and my apologies in advance, but 
> 
> Isn't this more a function of whatever language you are using and not a 
> SQLite issue?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Gregory Moore
thewatchful...@gmail.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[J.M. Royalty]

On 8/24/2011 9:05 PM, Gregory Moore wrote:
> I'm thinking it's parameters would be the name of a column and a character to 
> designate where the string wold be separated.  It would return multiple rows 
> and each row would contain a piece of the string.
>
> In other word, take a table like this:
>
> c1 c2
> ---
> 1  a; b; c
>
> run a statement like this:
>
> select c1, split(c2, ';')from t1;
>
> and get this:
>
> c1 c2
> ---
> 1   a
> 1   b
> 1   c
>
> Maybe SQLite can do it or not, i don't know.  I've searched using Google and 
> found code for a few different implementations but they are for other 
> dialects of SQL.  Here's a link to one such function i found:
>
> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
>
> Would that work with SQLite?
>
> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:
>
>> On 8/24/2011 9:36 PM, Gregory Moore wrote:
>>> Thanks for answering!  Can this not be added as a function?
>> What parameters would such a function take, and more interestingly, what
>> would its return value be?
>> -- 
>> Igor Tandetnik
>>
>>
>> Gregory Moore
>> thewatchful...@gmail.com
>>
lurker here, but felt compelled, and my apologies in advance, but 

Isn't this more a function of whatever language you are using and not a 
SQLite issue?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Gregory Moore]

I'm thinking it's parameters would be the name of a column and a character to 
designate where the string wold be separated.  It would return multiple rows 
and each row would contain a piece of the string.

In other word, take a table like this:

c1 c2
---
1  a; b; c

run a statement like this:

select c1, split(c2, ';')from t1;

and get this:

c1 c2
---
1   a
1   b
1   c

Maybe SQLite can do it or not, i don't know.  I've searched using Google and 
found code for a few different implementations but they are for other dialects 
of SQL.  Here's a link to one such function i found:

http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Would that work with SQLite?

On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:

> On 8/24/2011 9:36 PM, Gregory Moore wrote:
>> Thanks for answering!  Can this not be added as a function?
> 
> What parameters would such a function take, and more interestingly, what 
> would its return value be?
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Gregory Moore
thewatchful...@gmail.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Igor Tandetnik]

On 8/24/2011 9:36 PM, Gregory Moore wrote:
> Thanks for answering!  Can this not be added as a function?

What parameters would such a function take, and more interestingly, what 
would its return value be?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Gregory Moore]

Thanks for answering!  Can this not be added as a function?

On Aug 22, 2011, at 10:43 PM, Igor Tandetnik wrote:

> Gregory Moore  wrote:
>> I need to split up a list of items in a single row so they each have
>> their own row.
>> 
>> Basically I need to take this:
>> 
>> Key. Code
>> --
>> 1.  V1, v2, v3
>> 
>> And convert it to this:
>> 
>> Key. Code
>> --
>> 1.  V1
>> 1.  V2
>> 1.  V3
> 
> I don't think you can do this with SQL alone. You'll have to implement the 
> logic in your favorite programming language.
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Gregory Moore
thewatchful...@gmail.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Split Function for SQLite?

[Gregory Moore]

I need to split up a list of items in a single row so they each have
their own row.

Basically I need to take this:

Key. Code
--
1.  V1, v2, v3

And convert it to this:

Key. Code
--
1.  V1
1.  V2
1.  V3

After much googling I'm thinking I need a split function. I know
SQLite allows addition of functions. I did find a few different
implementations of split functions, but there were from full SQL and
SQL Server sites.  Do I need a split function designed specifically
for SQLite?  How would I go about adding the function so I can use it?
 Thanks!

Greg Moore
thewatchful...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Igor Tandetnik]

Gregory Moore  wrote:
> I need to split up a list of items in a single row so they each have
> their own row.
> 
> Basically I need to take this:
> 
> Key. Code
> --
> 1.  V1, v2, v3
> 
> And convert it to this:
> 
> Key. Code
> --
> 1.  V1
> 1.  V2
> 1.  V3

I don't think you can do this with SQL alone. You'll have to implement the 
logic in your favorite programming language.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Split Function for SQLite?

[Gregory Moore]

I need to split up a list of items in a single row so they each have
their own row.

Basically I need to take this:

Key. Code
--
1.  V1, v2, v3

And convert it to this:

Key. Code
--
1.  V1
1.  V2
1.  V3

After much googling I'm thinking I need a split function. I know
SQLite allows addition of functions. I did find a few different
implementations of split functions, but there were from full SQL and
SQL Server sites.  Do I need a split function designed specifically
for SQLite?  How would I go about adding the function so I can use it?
Thanks!

Greg Moore
thewatchful...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users