Take the 2-minute tour ×

I have a database table with a primary key called PremiseID.

Its MySQL column definition is CHAR(10).

The data that goes into the column is always 10 digits, which is either a 9-digit number followed by a space, like '113091000 ' or a 9-digit number followed by a letter, like '113091000A'.

I've tried writing one of these values into a table in a test MySQL database table t1. It has three columns

mainid integer
parentid integer
premiseid char(10)

If I insert a row that has the following values: 1,1,'113091000 ' and try to read row back, the '113991000 ' value is truncated, so it reads '113091000'; that is the space is removed. If I insert a number like '113091000A', that value is retained.

How can I get the CHAR(10) field retain the space character?

I have a programmatic way around this problem. It would be to take the len('113091000'), realize it's nine characters, and then realize a length of 9 infers there is a space suffix for that number.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

To quote from the MySQL reference:


So there's no way around it. If you're using MySQL 5.0.3 or greater, then using VARCHAR is probably the best way to go (the overhead is only 1 extra byte):

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

If you're using MySQL < 5.0.3, then I think you just have to check returned lengths, or use a character other than a space.

Probably the most portable solution would be to just use CHAR and check the returned length.

share|improve this answer

Q: How can I get the CHAR(10) field retain the space character?

Actually, that space is retained and stored. It's the retrieval of the value that's removing the spaces. (The removal of the trailing spaces on returned values is a documented "feature".)

One option (as a workaround) is to modify your SQL query to append trailing spaces to the returned value, e.g.

SELECT RPAD(premiseid,10,' ') AS premiseid FROM t1 

That will return your value with as a character string with a length of 10 characters, padded with spaces if the value is shorter than 10 characters, or truncated to 10 characters if its longer.

share|improve this answer

A standard CHAR(10) column will always have trailing spaces to pad out the string to the required length of 10 characters. As such, any deliberately trailing spaces will be blended in and, typically, stripped by your database adapter.

If possible, convert to a VARCHAR(10) column if you want to preserve the trailing spaces. You can do this with the ALTER TABLE statement.

share|improve this answer

Your Answer


