Re: [sqlite] line break, or similar, in query results
On 2014/10/14 16:19, Clemens Ladisch wrote: RSmith wrote: On 2014/10/14 13:09, Clemens Ladisch wrote: SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * FROM (SELECT ...); This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add something like '-\n' or '\r\n' as the Inserted item if char(10) causes headaches. sqlite> select '-\n'; -\n That's not very useful. ;-) and... On output produced for Windows systems that should also be char(13) || char(10). The sqlite3 shell uses the C runtime conventions, where stdout is in text mode, so char(10) is correct even on Windows. Indeed on both counts, but you assume he is using the sqlite3 shell and stdout... I said "might" be needed - if he produces, say for argument's sake, a csv-like file that will be opened by some text editor on Windows, the chr(13) is needed, and if he produces anything like a XML file or via script in Python or PHP or such the \r and \n (or even for HTML) will work as intended. Not all of us are so pro as to only use queries in the Linux command-line ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] line break, or similar, in query results
RSmith wrote: > On 2014/10/14 13:09, Clemens Ladisch wrote: >> SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * >> FROM (SELECT ...); > > This solution from Clemens will work perfectly, and depending on the > kind of OS you use and output method it might even work to add > something like '-\n' or '\r\n' as the Inserted item if > char(10) causes headaches. sqlite> select '-\n'; -\n That's not very useful. ;-) > On output produced for Windows systems that should also be > char(13) || char(10). The sqlite3 shell uses the C runtime conventions, where stdout is in text mode, so char(10) is correct even on Windows. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] line break, or similar, in query results
On 2014/10/14 13:09, Clemens Ladisch wrote: Paul Sanderson wrote: SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * FROM (SELECT ...); This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add something like '-\n' or '\r\n' as the Inserted item if char(10) causes headaches. On output produced for Windows systems that should also be char(13) || char(10). Have a great day! Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] line break, or similar, in query results
Thanks Clemens Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 14 October 2014 12:09, Clemens Ladisch wrote: > Paul Sanderson wrote: >> 1|0|texas >> 2|1|new york >> 3|2|washington >> 4|0|tampa >> 5|0|atlanta >> 6|5|charleston >> >> I'd like to add a break between groups in the results so it looks somethng >> like >> >> 1|0|texas >> 2|1|new york >> 3|2|washington >> >> 4|0|tampa >> >> 5|0|atlanta >> 6|5|charleston > > SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * > FROM (SELECT ...); > > > Regards, > Clemens > ___ > 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] line break, or similar, in query results
Paul Sanderson wrote: > 1|0|texas > 2|1|new york > 3|2|washington > 4|0|tampa > 5|0|atlanta > 6|5|charleston > > I'd like to add a break between groups in the results so it looks somethng > like > > 1|0|texas > 2|1|new york > 3|2|washington > > 4|0|tampa > > 5|0|atlanta > 6|5|charleston SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * FROM (SELECT ...); Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] line break, or similar, in query results
Another morning another question I have a query that performs a recursive query and outputs a varying number of rows using group by sqlite> with recursive path as (select id, previd, location from cities union all select cities.id, cities.previd, cities.location from path join cities on (path.previd = cities.id)) select * from path group by id; 1|0|texas 2|1|new york 3|2|washington 4|0|tampa 5|0|atlanta 6|5|charleston I'd like to add a break between groups in the results so it looks somethng like 1|0|texas 2|1|new york 3|2|washington 4|0|tampa 5|0|atlanta 6|5|charleston (it doesn't have to be a newline - just soemthng to make it more readable) * I know texas isn't a city :) Cheers Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
