CODEWITHSUNDEEP
mysqlsqlsubstringlongtext
maxm
maxm

Reputation: 5371

Limit length of longtext field in SELECT results

I'm executing a SELECT query on a table in MySQL using the command-line interface (not a GUI client):

SELECT * FROM blog_entry;

One of blog_entry's fields is of type 'longtext' and is such a long piece of text that when the result is displayed in my terminal the display of rows takes more than one line. This causes an ugly mess of a display, where columns aren't easily visible. What technique can I use in my SELECT query that would limit the number of characters displayed for each field so that the printed row results don't overflow to new lines?

Upvotes: 39

Views: 110515

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

You can use the LEFT() function to get only the first characters:

SELECT LEFT(LongField, 20) AS LongField_First20chars
FROM ...

Upvotes: 35

AgmLauncher
AgmLauncher

Reputation: 7270

The best way to clean up the readability of the results from a query in your terminal window is to use the mysql pager, not modifying your query as that can be too cumbersome.

  1. Set the pager:

    mysql> pager less -S

  2. Do your query:

    mysql> SELECT * FROM ...

This will put your results in a more readable format. You can use your arrow keys to page up and down and left and right to see the full table. Just press Q to get out of pager mode for that query, and then just run

mysql> pager more

to return to the normal output river if you want.

Upvotes: 16

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

Select Cast(theLongTextField As VarChar(100)) From blogEntry

Upvotes: 4

Oldskool
Oldskool

Reputation: 34837

Use MySQL's SUBSTRING function, as described in the documentation. Like:

SELECT SUBSTRING(`text`, 1, 100) FROM blog_entry;

To select first 100 chars.

Upvotes: 60

Related Questions