mysql trim column in database

Question

UPDATE mytable SET mycolumn= LTRIM(RTRIM(mycolumn));

i use this to trim entire column from leading and trailing space,but what i want is

|ID|Name      |
|1 |demo 6    |
|2 |demo3 500 |

what i want is to trim NAME where last char is int by removing this int.

output

|ID|Name      |
|1 |demo      |
|2 |demo      |

is it doable with mysql alone ? or i will need to use php to update table ?

note: this table contain around 20K row

Edit: ok php it is,

$s='';
foreach($q as $row){
$d=$row->name;
while(is_numeric(substr($d,-1))){$d=rtrim($d, '0123456789');}
if(is_numeric(substr($row->name,-1)))
$s.="update name_list set name='".$d."' where id='{$row->id}';\n";
}
echo "count:".count(explode('\n',$s))."<br/><pre>$s</pre>";

if i try to run them one by one it runout..

is there a better way to patch update rows?

proably quicker to do this in php, if this is a oneshot deal. you'd spend more time figuring out the sql-side string operations than it would do simply slurp that field into php and do `rtrim($field, '0123456789');`. — Marc B, Jan 15 '13 at 18:35
have you tried to SELECT INTO another table. BTW is this the same question as a year ago http://stackoverflow.com/questions/6072197/mysqltrim-all-fields-in-database — Mr. Radical, Jan 15 '13 at 18:37
@MarcB I agree that PHP is te way to go, but he might run in to trouble when the query takes to long to execute. — Mr. Radical, Jan 15 '13 at 18:39
@Mr.Radical exactly, i have 8000 row to update now!, how can i do it on single query ? or i have to do it one by one ? — Zalaboza, Jan 15 '13 at 18:46
@MomenMElZalabany I see that the problem is that you run out in other words it takes to long to execute. You could try to cut the operation into small pieces. E.g. where id between 1 and 50. That will still mean that you have to manually execute the query, but this is one time type of situation. — Mr. Radical, Jan 15 '13 at 19:11

Jason McCreary · Accepted Answer · 2013-01-15T21:16:19.057

1

Unfortunately MySQL currently (5.5) does not have a regular expression version of REPLACE(). Doing so in MySQL, while preferred and faster IMO, is not an option without a UDF or several nested calls to REPLACE()…

RTRIM(REPLACE(REPLACE(..., '8', ''). '9', ''))

Note: the above will replace all digits. Not just those to the right. Leaving for future readers, but will not work in the case of the OP.

As such, PHP is your only option. Borrowed from Marc B :

// loop over all rows
$no_ending_digits = rtrim($field, '0123456789');
// update row

edited Jan 15 '13 at 21:16

answered Jan 15 '13 at 18:39

Jason McCreary

71,546
23
135
174

but replace() will trash ALL digits in the field. op just wants right-hand-side digits removed. – Marc B Jan 15 '13 at 18:41

mysql trim column in database

1 Answers1