How to split a really long mysql result set into two lines?

Question

Suppose you have a result that is 100 chars long but you only have a 50 char width. How do you split a MYSQL result into two rows of 50 chars each?

Could you clarify the question a bit? Are you looking to insert 100 chars of data into a 50 char column? Or do you have 100 chars in the database but only have space in your app to display 50 chars?

I have 100 chars in the database result set but I want the result set string to have a break after the 50th char and continue onto the next line.

Example

SELECT * FROM FOO

returns 1 2 3 4 5 6 7 8 9...50 51 52 53..98 99 100

but I want 1 2 3 4 5 6 7 8 9...50 51 52... 99 100

Is this possible?

Answer 1

Do you want to return the result set in PHP or MySQL? If the former, then it's easier.

Take the string, and take the first 100 characters, put in a line break, and then the rest of the string.

MySQL would work on the same principle, but you may have issues with line-break characters.

Answer 2

SELECT substring(col, 1, 50) FROM foo
UNION ALL
SELECT substring(col, 51) FROM foo

Answer 3

Your'e asking a question about formatting data for viewing. SQL is a declarative data retrieval language, not a data pretty formatting language. You should solve this problem in your non-SQL code.

Formatting data in a SQL query is not a good idea, unless you have to write something that will run in a query analyzer. Your question isn't specific about whether or not that is the case.