1

For example: In MySQL. the data type Text, If we have a column defined as Text in a MySQL table, when to retrieve value of this column in JDBC, you get the value with excess trailing spaces, you can call String#trim(), but if such column is intended to accept space characters (hit space key when input). What can I do then?

MySQL Text is only for example, please consider other similar data types in other database.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
CaiNiaoCoder
  • 3,269
  • 9
  • 52
  • 82

3 Answers3

2

So, your question should be worded as: "How can I get rid of trailing spaces that my RDBMS puts into my fields?"

The answer is simple: never declare fixed-width columns such as CHAR(10) or NCHAR(10). These types were invented back in the dark ages of relational databases, and the only reason they still exist is for backwards compatibility with legacy systems.

Always use variable length width columns: VARCHAR, NVARCHAR, TEXT.

Now, if your columns are of variable width, and yet your fields contain trailing spaces, this means that you have converted your table from some older table which had fixed-width columns. There is nothing you can do other than read all of your fields, apply String.Trim() to them, and write them back to the database.

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
1

String.trim() will not delete white space inside the string.

RanRag
  • 48,359
  • 38
  • 114
  • 167
0

I think String.trim() does what you want, it deletes leading and trailing whitespace. The whitespace that is inside of the string will stay. Is this what you are asking?

Francis Upton IV
  • 19,322
  • 3
  • 53
  • 57
  • no, if user input 'ABC ' I want to keep the trailing spaces user inputted but wipe out all the trailing spaces filled by mysql. – CaiNiaoCoder Dec 31 '11 at 09:57
  • 1
    I don't think you can distinguish whitespace added by MySQL from whitespace that was entered by the user (I'm not sure why MySQL would add whitespace in a VARCHAR). – Francis Upton IV Dec 31 '11 at 09:58
  • Also, if the trailing spaces filled by MySQL is same thing as hitting space key, then this question have no answer. – CaiNiaoCoder Dec 31 '11 at 10:03
  • 1
    MySQL does't add whitespace in a VARCHAR, but Text. – CaiNiaoCoder Dec 31 '11 at 10:05
  • sorry for my bad english, make you hard to understand what I am talking about :D – CaiNiaoCoder Dec 31 '11 at 10:06
  • Your English is good enough, you said `Text`. However, I don't see [in the documentation](http://dev.mysql.com/doc/refman/5.6/en/blob.html) where whitespace is added by MySQL to the Text type. And of course the text type allows trailing or leading whitespace. It will pad with whitespace when doing an index comparison, but should not return that value that's padded. – Francis Upton IV Dec 31 '11 at 10:12