Page 1 of 2

trim space (" ")

Posted: Thu Sep 16, 2004 4:06 am
by ddragas
Hi all

In database is column mail. How to trim space (" ") from begining and from end of each record.

Posted: Thu Sep 16, 2004 4:31 am
by CoderGoblin
PHP command trim - trim before you place in DB
SQL (POSTRES) trim
So if you want to trim all rows

Code: Select all

UPDATE tablename set mail=trim(mail);

Posted: Thu Sep 16, 2004 5:33 am
by ddragas
Thank you for quick reply, but it doesn't work

Number affected rows= 0

here is code:

Code: Select all

<?php
include("../hr/con_db.php");

$sqlquery = "UPDATE mailovi SET mail = trim(mail)";
	
mysql_query($sqlquery) or die(mysql_error()); 

$broj_mailova_poslanih = mysql_affected_rows();

echo "updejtani redovi: " . $broj_mailova_poslanih;

include("../hr/disconect.php");
?>

<?php

?>

Posted: Thu Sep 16, 2004 5:38 am
by CoderGoblin
Try a ; at the end of the SQL statement.

Code: Select all

$sqlquery = "UPDATE mailovi SET mail = trim(mail);";
As I stated this works in Postgres. I am not sure what the command is for MySQL but it should be the same.

Posted: Thu Sep 16, 2004 5:59 am
by ddragas
I'm sorry to say but it doesn't work.

Posted: Thu Sep 16, 2004 6:22 am
by Jean-Yves
TRIM() should work fine in MySQL.

See manual:

http://dev.mysql.com/doc/mysql/en/Charset-result.html

Posted: Thu Sep 16, 2004 6:30 am
by CoderGoblin
OK, well I don't know why, maybe an MySQL bod could tell you....

Have you tried just the SQL statement, using SQL directly and see if it works ?

Posted: Thu Sep 16, 2004 8:02 am
by m3mn0n
[php_man]trim[/php_man] ()

[big_search]mysql database updating[/big_search]

Posted: Thu Sep 16, 2004 8:20 am
by ddragas
There is nothing wrong with statement

Code: Select all

<?php
$sqlquery = "UPDATE mailovi SET mail = trim(mail);";
<?php
?>
It is something else

I've imported in db from "csv" file these emails, and at the end of each record, i've putted "\r\n".

I think that is couse of my problem.

Posted: Thu Sep 16, 2004 8:21 am
by m3mn0n
Try using the WHERE cause as outlined on the pages I linked to.

A little research doesn't hurt.

Posted: Thu Sep 16, 2004 8:34 am
by ddragas
"\r\n" means

Carriage return: \r
Linefeed: \n

So here is nothing to trim. What I need is to delete "new row" from each record.

Only problem is I don't know how

Posted: Thu Sep 16, 2004 8:54 am
by CoderGoblin
Postgres only...

Code: Select all

UPDATE mailovi SET mail = btrim(mail,'\r\n');
Can't find btrim in MySQL manual though and don't have any method to test it anyway...
http://dev.mysql.com/doc/mysql/en/String_functions.html

Alternative: Check before you place it in the DB to start with.

Posted: Thu Sep 16, 2004 9:31 am
by ddragas
Found nothing

I've stoped in midle of problem

If somebody can help me

Regards-ddragas

Posted: Thu Sep 16, 2004 10:42 am
by ddragas
Come on folks

Any sugestions?

Posted: Thu Sep 16, 2004 11:13 am
by ddragas
CoderGoblin thank you for help


solved


truncated table and imported again