Page 1 of 1

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

Posted: Fri May 03, 2013 4:39 am
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.

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

Posted: Fri May 03, 2013 4:48 am
by simonmlewis

Code: Select all

UPDATE products SET code = REPLACE(code, 'ie%', 'us%')
I thought this might do it, but nope!

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

Posted: Fri May 03, 2013 1:07 pm
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%'

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

Posted: Fri May 03, 2013 1:11 pm
by simonmlewis
Will that replace 'ie'?

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

Posted: Fri May 03, 2013 1:27 pm
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?

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

Posted: Fri May 03, 2013 1:29 pm
by simonmlewis
Sorry -are you saying ur code will append us t the start, if it finds ie, and leave ie there?

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

Posted: Fri May 03, 2013 1:53 pm
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.

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

Posted: Fri May 03, 2013 2:02 pm
by simonmlewis
And replace the 'ie' at the start with 'us'? So no ie exists?

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

Posted: Fri May 03, 2013 3:13 pm
by Christopher
I think at this point the answer is just ... yes. ;)

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

Posted: Fri May 03, 2013 3:17 pm
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.

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

Posted: Fri May 03, 2013 4:22 pm
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%"

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

Posted: Fri May 03, 2013 4:32 pm
by simonmlewis
So substring is a means of saying "ignore the first two, and concate with these two instead" ?

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

Posted: Fri May 03, 2013 4:43 pm
by requinix
Not by itself. SUBSTRING does the "ignore the first two" part and CONCAT does the "concatenate with these two" part.

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

Posted: Fri May 03, 2013 4:46 pm
by simonmlewis
Sorry yes, that's what I meant. :)

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

Posted: Fri May 03, 2013 4:48 pm
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