[SOLVED] Help with replace and wildcards

[Topshed]

I am am still new to all this and I have hit a roadblock with a replace problem in mySQL

 

Using phpMyadmin Version  2.10.0.2

 

MySQL version 401

 

Table lner

 

Field  'notes', 'Tinytext' default NULL

 

Sample from notes  "ex Rod 5678 foo foo"

 

I want to replace all instances of "ex Rod"  with "ROD"

 

below are two of my most recent attempts both of which run but return 0 records updated

 

UPDATE lner
   SET notes = REPLACE(notes, 'ex Rod %', 'ROD %')
   WHERE notes LIKE 'ex Rod %'

UPDATE lner
   SET notes = REPLACE(notes, 'ex Rod', 'ROD')
   WHERE notes LIKE 'ex Rod'

 

Some help required please on my syntax

 

Thanks

 

Roy

 

 

 

 

 

[GingerRobot]

Try:

 

UPDATE lner
   SET notes = REPLACE(notes, 'ex Rod', 'ROD')
   WHERE notes LIKE '%ex Rod%'

[Topshed]

Thx for that  but I now get

Error

#1064 - You have an error in your SQL syntax;

 

Regards

Roy

[rajivgonsalves]

the syntax looks fine actually the SQL should be

 

 

UPDATE lner SET notes = REPLACE(notes, 'ex Rod', 'ROD') WHERE notes LIKE 'ex Rod%'

[Topshed]

Thank you problem solved

 

Regards

Roy