4

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 '01234567890';
+------------------------------------+
| text                               |
+------------------------------------+
| The quick brown fox jumps over the |
| 01234567890             |
+------------------------------------+
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 |
| 0123456 |
+----------------+

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 '01234567890') x;
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| 0123456        |
+-----------------------+
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?

2

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 '01234567890') 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               |
| 0123456        |
+-----------------------+
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 !!!

0

Try this one...

select substring('The quick brown fox jumps over the lazy dog',1,14 )text 
union select substring('0 1 2 3 4 5 6 7 8 9 0',1,13) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.