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
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