Limit columns to certain width in MySQL command

The mysql command gives nice tabular outputs that take into consideration half and full width spacing.

For example:

> select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０';
+------------------------------------+
| text                               |
+------------------------------------+
| The quick brown fox jumps over the |
| ０１２３４５６７８９０             |
+------------------------------------+
2 rows in set

Given this, how do I truncate a column to a certain width?

e.g. How do I get this?

+----------------+
| text           |
+----------------+
| The quick brow |
| ０１２３４５６ |
+----------------+

Edit: In terms of what I've tried.

If I attempt this by changing the query to use something like LEFT():

> select left(text, 7) text from (select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set

What I get isn't exactly what I wanted. I don't want 7 left characters of each string, I want both strings to show as many characters as optimally fit into a given width. I want a result similar to what's under "e.g. How do I get this?", if say this width was 14 (half-width characters).

MySQL does cope with different widths, as the first example establishes. (Look at it with a monospaced editor and the pipes line up.) Any solution like SQL Plus' column foo format a10, or using ENCODE() and BIT_LENGTH(), or user functions could work. The lack of answers probably means there isn't an easy solution?

Looks like a character set issue. Here is why:

I ran

select left(text, 7) text from
(select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;

in MySQL 5.6.22 for Windows. Here is that output

+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Same query in MySQL 5.6.21 for CentOS 6.3

+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set (0.00 sec)

You should use the CAST operator

select left(CONVERT(text USING latin1), 7) text from
(select 'The quick brown fox jumps over the' text union select '01234567890') x;

When I ran this in MySQL 5.6.22 for Windows, I got this

+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

When I ran this in MySQL 5.6.21 for CentOS 6.3, I got the same thing

+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Just hunt down the correct character set.
GIVE IT A TRY !!!

StackExchange Database Administrators Q#95031, answer score: 4