mysql weirdly formatting output

Question

mysql is weirdly formatting my output even though the table isnt overflowing with data in any way (only 30-4 rows, and 4 columns). Is there something I can do to adjust this?

mysql> select id, city, state, zip from location;
+----+----------------+-------+-------+
| id | city           | state | zip   |
+----+----------------+-------+-------+
   | 97227 |and       | OR
   | 95814 |mento     | CA
   | 94607 |nd        | CA
   | 90245 |gundo     | CA
   | 90015 |ngeles    | CA
   | 85004 |ix        | AZ
   | 84101 |Lake City | UT
   | 80204 |r         | CO
   | 78219 |ntonio    | TX
   | 77002 |on        | TX
   | 75219 |s         | TX
   | 73102 |oma City  | OK
   | 70113 |rleans    | LA
   | 60612 |go        | IL
   | 55403 |apolis    | MN
   | 53203 |ukee      | WI
   | 48326 |n Hills   | MI
   | 46204 |napolis   | IN
   | 44115 |land      | OH
   | 38103 |is        | TN
   | 33132 |          | FL
   | 32801 |do        | FL
   | 30303 |ta        | GA
   | 28202 |otte      | NC
   | 20004 |ngton     | DC
   | 19148 |delphia   | PA
   | 11217 |lyn       | NY
   | 10121 |ork       | NY
| 29 | Boston         | MA    | 2114  |
+----+----------------+-------+-------+
29 rows in set (0.00 sec)

It looks like all the state values end with carriage return, except for MA. — Barmar
– Barmar, Commented May 19, 2017 at 23:36

Barmar · Accepted Answer · 2017-05-19 23:40:30Z

3

Somehow you got carriage returns at the end of most of the state values. You can remove them with:

UPDATE location SET state = TRIM(TRAILING '\r' FROM state);

And you should investigate the code you use to add rows to this table, to see why it's leaving those characters in the data. You're probably using a file that was created on Windows and loading it into a program that runs on Unix. You can use the dos2unix command on Linux to fix all the newlines in a file. Or you can fix the program so it removes extraneous carriage return characters.

edited May 19, 2017 at 23:40

answered May 19, 2017 at 23:38

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql weirdly formatting output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related