Thread: extra spaces
I'm using PHP & PostgreSQL.
All values called from the database are still padded with extra spaces from
the column size in the database. Is this normal - I don't remember this
happening when I was using MySQL. I thought usually the database stripped
the extra spaces when it retrieved the values.
How do you recommend dealing with this, using a PHP command to strip every
value from the database - or can I change something in the database to have
it strip the spaces auto magic like?
- - - - - - -
- - - -
WARNING: Some experts believe that use of any keyboard may cause
serious injury. Consult Users Guide.
dfunct@telus.net
Make your fields to be varchar(x), not char(x) -alex On Thu, 14 Dec 2000, Soma Interesting wrote: > I'm using PHP & PostgreSQL. > > All values called from the database are still padded with extra spaces from > the column size in the database. Is this normal - I don't remember this > happening when I was using MySQL. I thought usually the database stripped > the extra spaces when it retrieved the values. > > How do you recommend dealing with this, using a PHP command to strip every > value from the database - or can I change something in the database to have > it strip the spaces auto magic like? > > > - - - - - - - > - - - - > WARNING: Some experts believe that use of any keyboard may cause > serious injury. Consult Users Guide. > dfunct@telus.net > >
On Thu, 14 Dec 2000, Soma Interesting wrote:
> I'm using PHP & PostgreSQL.
>
> All values called from the database are still padded with extra spaces from
> the column size in the database. Is this normal - I don't remember this
> happening when I was using MySQL. I thought usually the database stripped
> the extra spaces when it retrieved the values.
>
> How do you recommend dealing with this, using a PHP command to strip every
> value from the database - or can I change something in the database to have
> it strip the spaces auto magic like?
Sounds like you are using fixed length strings (char) rather than variable
length strings (varchar).
-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
A fool-proof method for sculpting an elephant: first, get a huge block of
marble; then you chip away everything that doesn't look like an elephant.
There's probably a trim() function in your database language. Use it in your select statement. MySQL, for varchar fields, trims them automatically. Maybe it does it to char as well. Miles Thompson Soma Interesting wrote: > I'm using PHP & PostgreSQL. > > All values called from the database are still padded with extra spaces from > the column size in the database. Is this normal - I don't remember this > happening when I was using MySQL. I thought usually the database stripped > the extra spaces when it retrieved the values. > > How do you recommend dealing with this, using a PHP command to strip every > value from the database - or can I change something in the database to have > it strip the spaces auto magic like? > > - - - - - - - > - - - - > WARNING: Some experts believe that use of any keyboard may cause > serious injury. Consult Users Guide. > dfunct@telus.net > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net
>>>>> "Soma" == Soma Interesting <dfunct@telus.net> writes:
Soma> All values called from the database are still padded with
Soma> extra spaces from the column size in the database. Is this
Soma> normal - I don't remember this happening when I was using
Soma> MySQL. I thought usually the database stripped the extra
Soma> spaces when it retrieved the values.
I guess I missed the orginal post about the problem, but...
Are your columns char() or varchar()? The former is padded in
PostgreSQL but not MySQL. The latter is what you really want.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
rbroberts@acm.org Forest Hills, NY 11375
On 15 Dec 2000, Roland Roberts wrote:
> >>>>> "Soma" == Soma Interesting <dfunct@telus.net> writes:
>
> Soma> All values called from the database are still padded with
> Soma> extra spaces from the column size in the database. Is this
> Soma> normal - I don't remember this happening when I was using
> Soma> MySQL. I thought usually the database stripped the extra
> Soma> spaces when it retrieved the values.
>
> I guess I missed the orginal post about the problem, but...
>
> Are your columns char() or varchar()? The former is padded in
> PostgreSQL but not MySQL. The latter is what you really want.
According to "Postgres: Introduction and Concepts", varchar is slower
than char. So if you (like me) want to use char and get rid of the
padding spaces, you may use a regex replacement, as in
while (@row=$result->fetchrow)
{
$row[0] =~ s/[\s]+$//;
}
in perl, or
$array["name"]=preg_replace("'[\s]+$'", "", $array["name"], -1);
in PHP.
--
Alvaro Herrera (<alvherre[@]protecne.cl>)
> > Soma> All values called from the database are still padded with
> > Soma> extra spaces from the column size in the database. Is this
> > Soma> normal - I don't remember this happening when I was using
> > Soma> MySQL. I thought usually the database stripped the extra
> > Soma> spaces when it retrieved the values.
>
> According to "Postgres: Introduction and Concepts", varchar is slower
> than char. So if you (like me) want to use char and get rid of the
> padding spaces, you may use a regex replacement, as in
>
> while (@row=$result->fetchrow)
> {
> $row[0] =~ s/[\s]+$//;
> }
>
> in perl, or
>
> $array["name"]=preg_replace("'[\s]+$'", "", $array["name"], -1);
>
> in PHP.
>
I'm new to the list so I'm not sure if this has already been said, but Perl
DBI has an option to trim trailing spaces in the connect statement. eg:
my $dbh = DBI->connect("dbi:Pg:dbname=mydb",
'myuser','mypass',
{RaiseError => 1, AutoCommit => 0, ChopBlanks => 1})
or die $DBI::errstr;
Cheers
Alvaro Herrera <alvherre@protecne.cl> writes:
> According to "Postgres: Introduction and Concepts", varchar is slower
> than char. So if you (like me) want to use char and get rid of the
> padding spaces, you may use a regex replacement, as in
> while (@row=$result->fetchrow)
> {
> $row[0] =~ s/[\s]+$//;
> }
I can confidently assure you that the performance differential between
char and varchar is small enough to be swamped out by invocation of
functions like the above. (Besides, it's gone entirely for PG 7.1.)
And that's just based on pure CPU-time considerations, without regard
for disk-transfer-time and cache-space-wastage issues. Padding
spaces can easily cost you FAR more along those dimensions than you
can ever hope to save from having a fixed tuple layout.
IMHO, 'char(n)' is suitable for extremely small values of n where
you *know* that space-padding is not the normal case. Almost the
only natural example I can think of is two-letter USA postal
abbreviations for states (such as PA in my address). Otherwise
use 'varchar(n)' if your application has a natural upper bound on
sensible string lengths, or 'text' if you can't instantly pick a
specific value of the upper limit 'n'.
regards, tom lane
On Wed, Dec 20, 2000 at 10:35:01AM +1100, Mark Cowlishaw wrote:
> > According to "Postgres: Introduction and Concepts", varchar is slower
> > than char. So if you (like me) want to use char and get rid of the
> > padding spaces, you may use a regex replacement, as in
> >
> > while (@row=$result->fetchrow)
> > {
> > $row[0] =~ s/[\s]+$//;
> > }
> >
> > in perl, or
> >
> > $array["name"]=preg_replace("'[\s]+$'", "", $array["name"], -1);
> >
> > in PHP.
i guess it would be better to use chop() (for trailing whitespaces) or
even trim() (strips whitespaces off begining as well)... it should be
considerably more effective then any regex...
--
Denis A. Doroshenko -- VAS/IN group engineer .-. _|_ |
[Omnitel Ltd., T.Sevcenkos st. 25, Vilnius, Lithuania] | | _ _ _ .| _ |
[Phone: +370 9863207 E-mail: d.doroshenko@omnitel.net] |_|| | || |||(/_|_