Page 1 of 1
How do you replace the first two chars in field for two?
Posted: Fri Apr 04, 2014 3:36 am
by simonmlewis
We have had a script running that enters codes into a stock field, but with two additional characters at the start, such as this:
es5656565
es3jgjr
esXL55
es7fg
I want to replace the "es" with "fr". There are hundreds. I'm sure there is a 'REPLACE' script but cannot quite work out how to do it. I'd say it should do it only if it finds 'es' as well, in case there are some where there is no 'es' in there.
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 04, 2014 3:50 am
by simonmlewis
Code: Select all
update products set romancode = concat( 'fr', substring(romancode, 3));
This does it - but how do I do it where the first two characters are "es"?
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 04, 2014 7:17 am
by Celauran
simonmlewis wrote:how do I do it where the first two characters are "es"?
Just add a where clause.
or
Code: Select all
WHERE SUBSTRING(romancode, 1, 2) = 'es'
(Yes, substring index starts at 1, not 0)
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 04, 2014 7:44 am
by simonmlewis
Thanks. I posted something else here about foreach loops. If you see it, can you take a look.
Properly stumped!!!!!
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 25, 2014 9:28 am
by simonmlewis
How do I remove the first two characters, only if they are "es"??
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 25, 2014 9:39 am
by Celauran
You mean like this?
Code: Select all
UPDATE tablename
SET romancode = SUBSTRING(romancode, 3)
WHERE SUBSTRING(romancode, 1, 2) = 'es'
Re: How do you replace the first two chars in field for two?
Posted: Fri Apr 25, 2014 10:28 am
by simonmlewis
That's it = thanks.