Strings with trailing blanks - where did I goof?

Hello all,

I am doing some post-conversion (Access->MySQL) comparison, and have
found a few classes of red flags, most of which are expected or
harmless. One potentially troublesome class is flagging comparisons of
'These strings are not equal ' vs. 'These strings are not equal' -
query results appear to be arriving with trailing blanks that are not
expected.

I read about and enountered this problem, and thought I had worked
around it. In truth, it's not a big deal, except that I want to
understand it and verify that it is harmless and/or correct it.

Am I guilty of a common abuse of field types?

Bill



Wilhelm K. Schwab, Ph.D.
University of Florida
Department of Anesthesiology
PO Box 100254
Gainesville, FL 32610-0254

Email: bills@anest4.anest.ufl.edu
Tel: (352) 846-1285
FAX: (352) 392-7029


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Strings with trailing blanks - where did I goof?

The CHAR and VARCHAR types in MySQL can not have trailing spaces. If you
insert data with trailing spaces, it will silently strip those off. It
appears that Access doesn't behave like this.

If it's important to keep the trailing spaces you can use the TINYTEXT,
or TINYBLOB types instead.

Chris

Bill Schwab wrote:

>Hello all,
>
>I am doing some post-conversion (Access->MySQL) comparison, and have
>found a few classes of red flags, most of which are expected or
>harmless. One potentially troublesome class is flagging comparisons of
>'These strings are not equal ' vs. 'These strings are not equal' -
>query results appear to be arriving with trailing blanks that are not
>expected.
>
>I read about and enountered this problem, and thought I had worked
>around it. In truth, it's not a big deal, except that I want to
>understand it and verify that it is harmless and/or correct it.
>
>Am I guilty of a common abuse of field types?
>
>Bill
>
>
>
>Wilhelm K. Schwab, Ph.D.
>University of Florida
>Department of Anesthesiology
>PO Box 100254
>Gainesville, FL 32610-0254
>
>Email: bills@anest4.anest.ufl.edu
>Tel: (352) 846-1285
>FAX: (352) 392-7029
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Strings with trailing blanks - where did I goof?

> 'These strings are not equal ' vs. 'These strings are not equal' -
> query results appear to be arriving with trailing blanks that are not
> expected.
If you want to use varchar field with trailing blanks you can add a
non-blank character to end of field.
Sample:
value = 'ABC ' (3 blanks)
to field you can store some like 'ABC .'
Or you can replace trailing blanks to unprintable character before put into
field. A good candidate is CHAR(0) or CHAR(160).

Petr Vileta, Czech republic


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Strings with trailing blanks - where did I goof?

Petr,

=======================
>>> "Petr Vileta" 12/23/04 08:14AM >>>
> 'These strings are not equal ' vs. 'These strings are not equal'
-
> query results appear to be arriving with trailing blanks that are
not
> expected.
If you want to use varchar field with trailing blanks you can add a
non-blank character to end of field.
Sample:
value = 'ABC ' (3 blanks)
to field you can store some like 'ABC .'
Or you can replace trailing blanks to unprintable character before put
into
field.
=======================

Actually, I was concerned about the appearance of unexpected trailing
spaces, not with how to keep them.

On further investigation, it appears that my error was in using
Microsoft products :) I need to look at it later with rested eyes, but
the spurious spaces appear to be coming from Access, not MySQL. When I
found the strings were different from the two databases, I _knew_ I had
made a mistake in the conversion; it now appears otherwise. I admit
that it would be easy to cross-wire the labels on the two databases, but
there are things that should be present only in the MySQL database (it
is a conversion plus new features), and those are labeled as coming from
MySQL.


=======================
A good candidate is CHAR(0) or CHAR(160).
=======================

For future reference, can you elaborate on this? My understanding is
the 4.1 is/was scheduled to make trailing space removal an optional
feature. I am content with the behavior as-is, but recognize that it
could be construed as a bug rather than a feature.

Thanks!

Bill



Wilhelm K. Schwab, Ph.D.
University of Florida
Department of Anesthesiology
PO Box 100254
Gainesville, FL 32610-0254

Email: bills@anest4.anest.ufl.edu
Tel: (352) 846-1285
FAX: (352) 392-7029


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Strings with trailing blanks - where did I goof?

Hi,

Petr Vileta wrote:
>>'These strings are not equal ' vs. 'These strings are not equal' -
>>query results appear to be arriving with trailing blanks that are not
>>expected.
>
> If you want to use varchar field with trailing blanks you can add a
> non-blank character to end of field.
> Sample:
> value = 'ABC ' (3 blanks)
> to field you can store some like 'ABC .'
> Or you can replace trailing blanks to unprintable character before put into
> field. A good candidate is CHAR(0) or CHAR(160).
>
You can't do this a "cmpstr" will still say that the strings are not equal.

As Bill already noticed, this is a problem caused by Access. I trim all
strings in Access to be more compatibel with other databases.

Erich

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Strings with trailing blanks - where did I goof?

Erich,

===================
You can't do this a "cmpstr" will still say that the strings are not
equal.

As Bill already noticed, this is a problem caused by Access. I trim all
strings in Access to be more compatibel with other databases.
===================

Thanks for confirming that Access is probably the guilty party. I still
plan to verify it all, but it's nice to know that others have observed
it. Again, the spaces are hamless; I simply don't like surprises when
it comes to data under my protection.

Bill



Wilhelm K. Schwab, Ph.D.
University of Florida
Department of Anesthesiology
PO Box 100254
Gainesville, FL 32610-0254

Email: bills@anest4.anest.ufl.edu
Tel: (352) 846-1285
FAX: (352) 392-7029


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org