0

In my Database, I made a small mistake by inserting a blank (space character) after a value like this:

INSERT INTO MYTABLE(country_name, code, value) values ('France', 'FR ', 345); -- id is autoincrement

When making a simple query in mySQLWorkbench:

SELECT * from MYTABLE;

The table shows

-------------------------------
| country_name | code | value |
-------------------------------
| France       | FR   |   345 |
-------------------------------
etc...

The space character is not visible in the output.

Because we were able to debug our application we found the extra space character but if you don't know what to look for, it is very difficult to catch that kind of error.

Is there any way to let mySQLWorkbench to display those special characters?

As a workaround I used this query:

SELECT t.id, CONCAT("'",  t.country_name, "'") AS Country, 
    CONCAT("'",  t.code, "'") AS CountryCode, 
    t.value
FROM MYTABLE t

But you have to know which column is a VARCHAR and which are not (e.g. value is a number).

The version I use is: MySQLWorkbench 6.0.8.11354 build 833

0

I doubt if there is an easier way than that CONCAT to see the spaces. (And that will fail for various esoteric characters.)

Here's another way; it is more practical for more complex situations:

SELECT HEX(`code`) ...

It will return 465220. for "FR ".

Declare it this way:

code CHAR(2) CHARACTER SET ascii NOT NULL

The (2) will truncate to 2 characters (perhaps with a warning); the rest is appropriate for a "country code" column.

(When asking questions, specify MySQL version.)

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.