Re: [sqlite] Limit Column Width
Dennis Cote wrote: > You can use the replace() function to strip the newlines from your > strings. You could replace the newlines with empty strings or with a > single space. The trick is entering the newlines. You can enter them > directly in an sql script file. > > select replace(some_column, ' > ',' ') from a_table; > > Or you can use a cast and enter the expected newline characters for your > system (i.e. Windows is 0d0a, *nix is 0a, etc). > > select replace(some_column, cast(x'0d0a' as text), ' ') from table > > Then the columns can be truncated using the substr() function as you > suggest. The resulting strings should fit in your grid. Thanks Dennis! Works great. This is what I came up with: NOTES="$(cmd2db ".mode column" ".width 3 80" ".headers off" "SELECT id,replace(substr(note,1,80),cast(x'0a' as text),' ') FROM notes WHERE type LIKE '%$TYPE%'$SUBTYPE LIMIT $LMT;")" echo"$NOTES"|sed -e 's/ / /g' I'm using sed to replace tabs with a space.I suppose I could use another replace-cast in the SQL. cmd2db is a really simple function to pass multiple commands to sqlite. I'm sure there is a better way, but this works. NOTES is a table in my personal information manager to store random text. Fred Stephens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit Column Width
Fred J. Stephens wrote: > How can I limit the width of column returned by a query? > In a bash script I am returning an ID, and a text field from a table. > The text field can be any length, contain newlines, etc. The problems is > if a row of results contains a new line, the "grid" of rows and columns > is messed up. I can limit the length of the returned text with > substr(test,1,80), but that doesn't stop the newline appearing in the > output. Neither does .mode column, .width 3 80 (3 for the ID, 80 for the > text). > I have tried dozens of combinations of piping the output to sed & tr to > try and remove any newlines, but if I do so, the output "grid" is all > crunched together with the next ID on the same row as the previous text. > > What I need is for each row of the query results to end at 80 characters > or so and not wrap to another line - ie: one row per line. > I hope I am making sense here. > Any suggestions appreciated. You can use the replace() function to strip the newlines from your strings. You could replace the newlines with empty strings or with a single space. The trick is entering the newlines. You can enter them directly in an sql script file. select replace(some_column, ' ',' ') from a_table; Or you can use a cast and enter the expected newline characters for your system (i.e. Windows is 0d0a, *nix is 0a, etc). select replace(some_column, cast(x'0d0a' as text), ' ') from table Then the columns can be truncated using the substr() function as you suggest. The resulting strings should fit in your grid. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Limit Column Width
How can I limit the width of column returned by a query? In a bash script I am returning an ID, and a text field from a table. The text field can be any length, contain newlines, etc. The problems is if a row of results contains a new line, the "grid" of rows and columns is messed up. I can limit the length of the returned text with substr(test,1,80), but that doesn't stop the newline appearing in the output. Neither does .mode column, .width 3 80 (3 for the ID, 80 for the text). I have tried dozens of combinations of piping the output to sed & tr to try and remove any newlines, but if I do so, the output "grid" is all crunched together with the next ID on the same row as the previous text. What I need is for each row of the query results to end at 80 characters or so and not wrap to another line - ie: one row per line. I hope I am making sense here. Any suggestions appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
