Can you replace 2 char at start of string, with 2 others?
Moderator: General Moderators
-
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?
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.
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.
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
Code: Select all
UPDATE products SET code = REPLACE(code, 'ie%', 'us%')Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you replace 2 char at start of string, with 2 others
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
Will that replace 'ie'?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: Can you replace 2 char at start of string, with 2 others
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?
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
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.
All the best from the United Kingdom.
Re: Can you replace 2 char at start of string, with 2 others
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.
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
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.
All the best from the United Kingdom.
- 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
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
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.
All the best from the United Kingdom.
Re: Can you replace 2 char at start of string, with 2 others
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
The MySQL version of that is
Then you can update all the rows in the entire table... but of course only the ones that start with "ie".
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);Code: Select all
CONCAT("us", SUBSTRING($old, 3))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
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.
All the best from the United Kingdom.
Re: Can you replace 2 char at start of string, with 2 others
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
Sorry yes, that's what I meant. 
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: Can you replace 2 char at start of string, with 2 others
You really know your stuff.
Would appreciate you lookin at my other thread here hich has me stumped.
viewtopic.php?f=2&t=137761
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.
All the best from the United Kingdom.