Posted by Andy Hassall on 06/18/05 15:43

On Sat, 18 Jun 2005 07:16:07 -0500, Terry <tknospa@wejuggle2.com> wrote:

>Geoff Berrow wrote:
>> I noticed that Message-ID: <42b378b7$1_1@spool9-west.superfeed.net> from
>> Terry contained the following:
>>
>>>I apparently do not correctly understand varchar - everything I have
>>>read indicates that text entered into a MySQL table with varchar
>>>characteristics will automatically have any space characters stripped
>>
>>>from both ends of the line.
>>
>> Where have you read that? A space is just another character. If you
>> want to strip them, use trim().
>>
> From page 123 of "PHP and MySQL for Dynamic Web Sites" by Larry Ullman:
>---------
>Heading "Char vs. Varchar"
>One primary difference between the two is that anything stored as CHAR
>will always be stored as a string the length of the column (using spaces
>to pad it). Coversely, VARCHAR strings will only be as long as the
>stored string itself.
>---------
> - a google search yielded simular results.

That doesn't say VARCHAR will trim spaces; it's saying the opposite thing
about CHAR datatypes - the CHAR datatype PADs with spaces.

>The filename (row[0]) is varchar and has no extra spaces.
>Some of the entries (row[3]) have leading spaces - most do not.
>Additionaly, I did not put any spaces in - why should I have to strip them?

mysql> create table t (c varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values ('none');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (' leading');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('trailing ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (' both ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+----------+
| c |
+----------+
| none |
| leading |
| trailing |
| both |
+----------+
4 rows in set (0.01 sec)

If the spaces are there it's because they were in the original data when
inserting them.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]