This is follow on from a couple of other tips that I have put up recently
This is another follow on, I suppose you could call this a mini series :-).
After importing the database I could not see all the data in all the columns. When I done select * from , it looked garbled and would not show all the information.
The row count was correct, so I then ran the command again but this time instead of ending the statement with ; (semi colon), I used \G . When you use \G instead of ; it shows the data in Raw Form, not in table view. From here is looked ok, but I still wanted to see why it was showing messed up.
Next was to change the way I view the data, so I used pager, this allows you to use OS commands and formatting like less and more in Linux. The statement I chose was pager less -n -i S.
The options mean
-i ignore case
-n suppress line numbers
-S Chop long lines
From here when the table was displayed it showed the last column had the data followed by ^M. Because of this control character that is what was messing the display up.
A quick update statement to set the columns to what they were supposed to be solved the problem. In this case the column had Regions in it, so a few simple updates update main_db set region = ‘EMEA’ where region like EME%’, repeated with NA and APAC, this sorted the issue. Once updated I could easily select * from and see all the data.
I am always interested in feedback so please feel free to add any comments, or you can mail me here. If you would like to submit a quick tip with full credit and links back to your site then also feel free to contact me.