2

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(".", " ");
Improve this question
4
  • Could you provide a test code (or at least the table desc)?
    – Dimitre Radoulov
    Commented Oct 10, 2011 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
    Commented Oct 10, 2011 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
    Commented Oct 11, 2011 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
    Commented Oct 11, 2011 at 9:10

1 Answer 1

Reset to default
4

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.

Improve this answer
1
  • VARCHAR should keep your whitespaces. Just confirmed on MySQL version 5.1.56.
    – Jon
    Commented Nov 14, 2011 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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