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? >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
