JChing JChing - 27 days ago 6
MySQL Question

MySQL: how to trim the content of a cell to make SELECT output more readable

I have a MySQL table with 6 columns. Some of the columns contain long JSON strings that have newlines and spaces. When I list the content of the table with the SELECT statement, the output is very messy.

Is there a command (or default setting I can change), to limit the output of each column to the first few meaningful characters, so that each row will show as single line regardless of the cell content? Something like:

+---------------------------+-------------------------+---------+-----------+--------------+----------+
| jsondata | column2 | column3 | column4 | column5 | column6 |
+---------------------------+-------------------------+---------+-----------+--------------+----------+
| { "Text":[{"user_id":"3","| ABCDEFABCDEFABCDEFABC | 3 | ABCABCA | txt | { "email"|
+---------------------------+-------------------------+---------+-----------+--------------+----------+

Answer

I don't know about settings that you can permanently change in the DB, but something like this should format the output as you describe it:

SELECT column2, column3, column4, column5, REPLACE(CAST(jsondata AS CHAR(20)), '\n', '') AS jsondata FROM tablename;