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.
How do you replace the first two chars in field for two?
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
How do you replace the first two chars in field for two?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you replace the first two chars in field for two?
Code: Select all
update products set romancode = concat( 'fr', substring(romancode, 3));Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you replace the first two chars in field for two?
Just add a where clause.simonmlewis wrote:how do I do it where the first two characters are "es"?
Code: Select all
WHERE romancode LIKE 'es%'Code: Select all
WHERE SUBSTRING(romancode, 1, 2) = 'es'-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you replace the first two chars in field for two?
Thanks. I posted something else here about foreach loops. If you see it, can you take a look.
Properly stumped!!!!!
Properly stumped!!!!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you replace the first two chars in field for two?
How do I remove the first two characters, only if they are "es"??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do you replace the first two chars in field for two?
You mean like this?
Code: Select all
UPDATE tablename
SET romancode = SUBSTRING(romancode, 3)
WHERE SUBSTRING(romancode, 1, 2) = 'es'-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: How do you replace the first two chars in field for two?
That's it = thanks.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.