Need help with search and replace in MYSQL 1

[Spanjis]

I want to search through some text and find where width is bigger than 200 and replace it with 300. I'm using MySQL 4.1.11.

This is what i got so far.

update `Artikkel`
set `Hoved` = replace(`Hoved`, regexp 'width="(.*)"', 'width="300"')
where convert(int, substring(`Hoved`, instr(`Hoved`, 'width') + 7, 3)) > 200;

But i get this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'regexp 'width="(.*)"', 'width="300"')
where convert(int, substring(`Hoved`, i' at line 2

Any ideas?

 

[r937]

assuming your WHERE condition is working correctly, and assuming your existing widths are no longer than 4 characters,

update Artikkel
set Hoved
= concat( left(Hoved,instr(Hoved,'width')+5)
, '300'
, left(Hoved,instr(Hoved,'width')+8) )
where convert(int
, substring(Hoved,instr(Hoved,'width')+7,3))>200;



r937.com | rudy.ca

 

[TonyGroves]

REGEXP only applies to matching, not substitution. However, it should be possible to set `Hoved` to a concatenation of the bit before the figure, then 300, then the bit after the figure.

 

[TonyGroves]

Sorry, Rudy got there first.

 

[Spanjis]

thanks for quick reply, doesnt look like it's working, getting this error know:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int
, substring(Hoved,instr(Hoved,'width')+7,3))>200' at line 6

 

[Spanjis]

Soz, I had convert wrong, it should be this:
convert(substring(Hoved, instr(Hoved, 'width') + 7, 3), unsigned)

looks like it works know, thanks

 

[r937]

well, i did say "assuming..."

perhaps try the CAST function

where cast(
substring(Hoved,instr(Hoved,'width')+7,3)
as integer) > 200;


r937.com | rudy.ca