Re: varchar problem

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

>Geoff Berrow wrote:
>> I noticed that Message-ID: <42b378b7$1_1@xxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxx> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
.

Relevant Pages

  • Re: Inserting an Email Address fails.
    ... 'Position' VARCHAR NOT NULL, ... "You have an error in your SQL syntax. ... corresponds to your MySQL server version for the right syntax to use ...
    (perl.dbi.users)
  • HELP: moving DB to another server probs :(
    ... Upon importing into the new MySQL server I get this error messsage. ... I had a look at MySQL website but to no avail I still did not know what ... filepath varchar(255) NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: checkbox values stored in mysql
    ... you need to review the MySQL docs concerning the CHAR/VARCHAR data types at: ... There is a table that shows what the difference between CHAR and VARCHAR - read ... Whoever gave you that sage advice obviously should not be designing databases. ... MySQL and PostgreSQL are fine for some ...
    (comp.lang.php)
  • MYSQL - PHP
    ... First of all sorry if this is not the correct newsgroup for this question, ... but I am using PHP with MYSQL and someone here could have an answer and the ... bak_content_title varchar ...
    (comp.lang.php)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)