Mysql – Limit length of longtext field in SELECT results

longtextMySQLsql

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?

Best Answer

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

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

To select first 100 chars.

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

Oracle
MS SQL Server
MySQL
Postgres
SQLite v3
Teradata
DB2
Sybase
Vertica
HSQLDB
H2
AWS RedShift
SAP HANA
Google Spanner

Sql – How to perform an IF…THEN in an SQL SELECT

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Best Answer

Related Solutions

Sql – Insert into … values ( SELECT … FROM … )

Sql – How to perform an IF…THEN in an SQL SELECT

Related Topic