Posted by william on 01/03/07 17:04

On Wed, 03 Jan 2007 07:37:02 -0800, monomaniac21 wrote:

> hi all
>
> using preg_replace
>
> how can i replace the letter i in a string with nothing (delete it)
> when it is the last letter or it is followed by an i?
>
> i have products that are listed in a db with i or ii as in 320ii and i
> want to strip out the i's at the end when displaying the product name
>
> thanks
>
> marc

if you have a huge database it might be better to use stored procedure :

(vars between "{}" have to be adapted to your table/field)



DELIMITER ?
CREATE PROCEDURE sp ()
BEGIN
DECLARE id INT;
DECLARE currentid INT DEFAULT 0;
DECLARE productname VARCHAR(128);
DECLARE newproductname VARCHAR(128);
DECLARE maxid INT DEFAULT 0;
SELECT max({product_id}) INTO maxid FROM {product} where {product_id} >
current AND {product_name} REGEXP 'ii$';

WHILE ( current < maxid+1 ) DO

SELECT {product_id},{product_name} INTO id,productname FROM {product}
where {product_id} > current AND {product_name} REGEXP 'ii$' LIMIT 1;
newproductname = LEFT(productname,LENGTH(productname)-1);

UPDATE {product} set {product_name} = newproductname WHERE {product_id}
=id;

current=id;

END WHILE;
END?
DELIMITER ;

[Back to original message]