Remove variable substrings from fields

[federico07]

Hello,

I have a table as follows:

idProductBrand

1Pepsi prod1Pepsi

2Fanta prod2Fanta

3Coke prod3Coke

I need to remove the Brand prepended to the text included in Product column.

I need to get:

idProductBrand

1prod1Pepsi

2prod2Fanta

3prod3Coke

 

I tried the following query:

 

mysql_query("UPDATE table SET Product=REPLACE(Product, '$Brand', '')") or die(mysql_error());

 

but it does not work (it replaces only in the last row).

Is correct when placing a variable ($Brand) in the text to be replaced?

 

Does anybody know any UPDATE commands working on that?

 

Thanks

[Ken2k7]

That's close.

 

Try this -

UPDATE table SET Product = REPLACE(Product, Brand, '');

[federico07]

Now it works perfectly.

 

Ken2k7, thanks for your advice !

[fenway]

You might want to TRIM() that, too.

[federico07]

It seems only case sensitive.

 

Any way to get it working with a case insensitive search/replace?

 

Thanks

[fenway]

Just UPPER() each part of the replace.

[federico07]

Thanks. I tried this:

UPDATE table SET Product=REPLACE(Product, (UPPER(Brand)), '');

 

However the problem is the text I need eliminating -- referred to the example above -- could be in format PEPSI, pepsi, Pepsi. So I would need something 'universal' doing the job.

[fenway]

You didn't upper() product.