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(".", " ");
  • Could you provide a test code (or at least the table desc)? – Dimitre Radoulov Oct 10 '11 at 14:04
  • 1
    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.

  • VARCHAR should keep your whitespaces. Just confirmed on MySQL version 5.1.56. – jonvan Nov 14 '11 at 15:29

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.