[SOLVED] @#$% Replace statement

[Topshed]

Every now and then I have problems with the REPLACE statement

 

Using phpMyadmin Version  2.10.0.2

 

MySQL version 401

 

Table G

 

Field  'wdrn', CHAR [8] default NULL

 

Sample from wdrn I want to change  "Feb-195 "

 

I have been trying  to replace all instances of '195 '  with '1953' where a condition applies in another field

 

 

UPDATE G Set wdrn = REPLACE(wdrn,'195 ','1953') WHERE chassis LIKE 'Guy Arab II'
// Fails !

UPDATE G Set wdrn = REPLACE(wdrn,'195','1953') WHERE chassis LIKE 'Guy Arab II'
// Works but replaces every instance even when date already has four digits ie: 1951

 

I have just destroyed half a days work because it destroyed all my other dates rather then just the ones with the 3 missing

 

Any help would bevery welcome

 

Thanks

Roy...

[trq]

orks but replaces every instance even when date already has four digits ie: 1951

 

Of course it will also replace 1951 with 19531, look at the logic. Does your database actually contain wdrn fields that are '195 ' ? Not likely.

 

You would need something like....

 

UPDATE G Set wdrn = REPLACE(wdrn,'195 ','1953') WHERE chassis LIKE 'Guy Arab II' && LENGTH(wdrm) <= 3; 

[Topshed]

Hmm thank you for that, as you can see I tried that statement but it failed

and did the same with your additions

 

Statement 2 worked (wrongly) but not with the additions, both reported  Affected rows: 0

but showed no errors,

 

UPDATE G Set wdrn = REPLACE(wdrn,'195','1953') WHERE chassis LIKE 'Guy Arab II' && LENGTH(wdrn) <= 3 

 

So I am still in trouble

 

re3egards

Roy..

 

[Topshed]

Ooopps silly me

 

My problem after your input was the field size is 8

therefore I just needed to g=cange the end to LENGTH(wdrn) <= 7

 

Fixed... than you soo much

 

Regards

Roy...