Can you replace 2 char at start of string, with 2 others?

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:

Can you replace 2 char at start of string, with 2 others?

Post by simonmlewis »

We have 766 rows in a DB. Each have a value in a romancode field, such as, ie6573875, and we want to find the first two characters, "ie", and replace them with "us".

I would just do a simple Find and Replace IE for US, but there may be codes like this: ieXLPPIE24.

So it has to be TWO characters at the start only.

I thought it would be a CONCAT statement, it's not - it's a REPLACE. But I don't know how to do a REPLACE in a specific position in the string. It's ALWAYS in the same place in the string.
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: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

Code: Select all

UPDATE products SET code = REPLACE(code, 'ie%', 'us%')
I thought this might do it, but nope!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you replace 2 char at start of string, with 2 others

Post by requinix »

It's not a REPLACE. It's a "start with 'us' and add everything after the first two characters".

Code: Select all

UPDATE products SET code = CONCAT('us', SUBSTRING(code, 3)) WHERE code LIKE 'ie%'
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

Will that replace 'ie'?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you replace 2 char at start of string, with 2 others

Post by requinix »

It's still not a REPLACE. You don't want to do that because then it will replace all the "ie". What you want is to take your own "us" and append the rest of the code after the "ie".

But to answer the question, have you tried it?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

Sorry -are you saying ur code will append us t the start, if it finds ie, and leave ie there?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Can you replace 2 char at start of string, with 2 others

Post by twinedev »

UPDATE products SET code = CONCAT('us', SUBSTRING(code, 3)) WHERE code LIKE 'ie%'

It will find act upon all records that START with "ie"

UPDATE products SET code = CONCAT('us', SUBSTRING(code, 3)) WHERE code LIKE 'ie%'

This function places all the items passed to it together, similar to in php doing: 'us' . $variable

UPDATE products SET code = CONCAT('us', SUBSTRING(code, 3)) WHERE code LIKE 'ie%'

This will get the value of the given string (code field), starting at the 3rd character (ex ieXLPPIE24 )

All together it will update all rows where code starts with ie and will update that field to be the string 'us' concatenated with everything after the ie in the field.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

And replace the 'ie' at the start with 'us'? So no ie exists?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Can you replace 2 char at start of string, with 2 others

Post by Christopher »

I think at this point the answer is just ... yes. ;)
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

I understand you, bu concatenation just taps it on the end. Replace, replaces something. So I don't see or understand that. And I want to understand plz.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you replace 2 char at start of string, with 2 others

Post by requinix »

Alright, one more try.

I'm trying to steer you away from the word "replace" because the most obvious function, REPLACE, will search a string for all the instances of a particular substring and then replace each one with another substring. REPLACE("ie", "us", "iexlppie24") will result in "usxpllus24". You've already realized this is a problem.

So don't think of it as a "replace". Think of it another way: you've got a string with stuff, you want to ignore the first two characters, and instead use two different characters. Thinking of it literally like that the result is "okay, start with the two characters I want to use and concatenate them with everything after the first two characters in the other string". With PHP that's like

Code: Select all

$old = "iexlppie24";
$new = "us" . substr($old, 2);
The MySQL version of that is

Code: Select all

CONCAT("us", SUBSTRING($old, 3))
Then you can update all the rows in the entire table... but of course only the ones that start with "ie".

Code: Select all

UPDATE products SET code = CONCAT("us", SUBSTRING(code, 3)) WHERE code LIKE "ie%"
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

So substring is a means of saying "ignore the first two, and concate with these two instead" ?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you replace 2 char at start of string, with 2 others

Post by requinix »

Not by itself. SUBSTRING does the "ignore the first two" part and CONCAT does the "concatenate with these two" part.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

Sorry yes, that's what I meant. :)
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: Can you replace 2 char at start of string, with 2 others

Post by simonmlewis »

You really know your stuff.
Would appreciate you lookin at my other thread here hich has me stumped.
viewtopic.php?f=2&t=137761
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply