Limit length of longtext field in SELECT results

Question

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?

score 59 · Accepted Answer · answered Feb 11 '12 at 23:48

59

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

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

To select first 100 chars.

answered Feb 11 '12 at 23:48

Oldskool

34,211
7
53
66

4

I found that the single inverted commas around 'text' was not required if 'text' is a field name – Craig Watkinson May 17 '15 at 09:46

score 33 · Answer 2 · answered Feb 11 '12 at 23:48

33

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

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

answered Feb 11 '12 at 23:48

ypercubeᵀᴹ

113,259
19
174
235

1

You don't really need the `as LongField_First20chars` unless you want to make it easier to use from a program. – poolie May 08 '16 at 00:48
2

@poolie of course you don't. But it's good practice to have an alias in the returned columns. – ypercubeᵀᴹ May 08 '16 at 07:47
Quote the AS clause for pretty column names: "LongField (short view)". – TonyG Feb 21 '22 at 21:11

AgmLauncher · Answer 3 · 2014-01-15T18:15:16.677

14

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.

Set the pager:

mysql> pager less -S
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.

edited Jan 15 '14 at 18:15

answered Jan 15 '14 at 17:49

AgmLauncher

7,070
8
41
67

Seems like `-S` would chop the whole line, not each column. – Rick James Apr 13 '23 at 20:40
Rick, it does seem to chop (needed) columns in an intelilgent manner (IDK how) – Fattie Apr 19 '23 at 19:48

score 4 · Answer 4 · answered Feb 11 '12 at 23:49

4

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

answered Feb 11 '12 at 23:49

Bassam Mehanni

14,796
2
33
41

Limit length of longtext field in SELECT results

4 Answers4