Page 1 of 1

to many spaces :s

Posted: Mon Jan 17, 2005 9:20 am
by potato
Hi everyone,

i've got the following 'pain in the ass' problem:
i had added for about 800 students in my mysql-db.
For that, i used copy paste for the names. Now, between the last and forname there are more than one spaces.
Example:

Code: Select all

Behets      Tom
I dont want to manually change all the 800 names to delete the extra spaces. Anybody knows a sollution to automatically detect if there are more than one spaces, and if yes, make just oner of it?

Friendly greetings from belgium ;)
tom


feyd | added code tags to show detail

Posted: Mon Jan 17, 2005 9:27 am
by feyd
something like this should do it:

Code: Select all

<?php

// put your connect here
$sql = 'SELECT `name`,`id` FROM `table`';

$query = mysql_query($sql) or die(mysql_error());

$sql2 = 'UPDATE `table` SET `name` = ''%s'' WHERE `id` = ''%d''';

while($row = mysql_fetch_assoc($query))
&#123;
  mysql_query(sprintf($sql2, preg_replace('#\s+#', ' ', trim($row&#1111;'name'])), $row&#1111;'id']);
&#125;
replace the column and table references with your columns and table names.

Posted: Mon Jan 17, 2005 11:59 am
by Weirdan
or something like this:

Code: Select all

update table_name set name=insert(name, locate(' ', name),length(name) - locate(' ', reverse(name)), ' ');
Edit: feyd's snippet is more flexible.

Posted: Tue Jan 18, 2005 1:54 am
by potato
THANX TO ALL OF YOU GUYS. I am registered on many more forums, and i have to say, devnetwork RULEZZZ. Fast response, really, i like it!