How do you replace the first two chars in field for two?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
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?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
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?

Post 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"?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you replace the first two chars in field for two?

Post by Celauran »

simonmlewis wrote:how do I do it where the first two characters are "es"?
Just add a where clause.

Code: Select all

WHERE romancode LIKE 'es%'
or

Code: Select all

WHERE SUBSTRING(romancode, 1, 2) = 'es'
(Yes, substring index starts at 1, not 0)
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?

Post by simonmlewis »

Thanks. I posted something else here about foreach loops. If you see it, can you take a look.
Properly stumped!!!!!
Love PHP. Love CSS. Love learning new tricks too.
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?

Post by simonmlewis »

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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do you replace the first two chars in field for two?

Post by Celauran »

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?

Post by simonmlewis »

That's it = thanks.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply