Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to enter the record with white-space as a record in MySQL. I had tried to enter it through shell and phpmyadmin, the record enters but when I try to retrieve the record it returns as null. I had also set the column null = no even then returns nothing.

code is below through shell and php:

insert into first_replace(to_replace, with_replace) values(".", " ");
share|improve this question
Could you provide a test code (or at least the table desc)? – Dimitre Radoulov Oct 10 '11 at 14:04
Table's show create output is needed here. Varchar strips whitespace so if that is what you have, it is probably stripping this output entirely – TechieGurl Oct 10 '11 at 14:55
I believe TechieGurl's assertion about trimming whitespace is only applicable to version <= 5.0.x. However whitespace is ignored when doing comparisions. Meaning where col="abc" will match both "abc" and "abc ". – atxdba Oct 11 '11 at 2:07
Perhaps you should approach the problem in another way; rather than including white space in your string replacement definition how about storing a setting? E.g. "trim_string" (0 or 1) (false / true) etc. – Tony Oct 11 '11 at 9:10

You should use VARCHAR instead of CHAR for data type, if you want to store spaces. Space (whitespace) retention is documented in MySQL online documentation:

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

share|improve this answer
VARCHAR should keep your whitespaces. Just confirmed on MySQL version 5.1.56. – jonvan Nov 14 '11 at 15:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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