How do I replace just the first 2 characters in all fields?

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 I replace just the first 2 characters in all fields?

Post by simonmlewis »

I need to find all fields where the first two characters are "ie", and replace those characters with "uk".

I thought I had sorted the part of replacing with this, but it makes it either go uke, or iee, depending on how I Adjust the code:

Code: Select all

UPDATE products SET romancode = CONCAT( REPLACE( LEFT( romancode, 2 ) , 'uk', 'ie' ) , SUBSTRING( romancode, 2, CHAR_LENGTH( romancode ) ) ) 
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: How do I replace just the first 2 characters in all fiel

Post by requinix »

Sounds like it's replacing fine but using the second character onwards instead of the third character.

Huh. "Second character". There's a 2 in your SQL. I wonder if...
For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
SUBSTRING


And with that out of the way, you made the query quite a bit more complicated than it needed to be. Surely "find all the values that start with 'uk' and make them start with 'ie'" is a simpler way of expressing the problem?

Code: Select all

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

Re: How do I replace just the first 2 characters in all fiel

Post by simonmlewis »

UPDATE products SET romancode = CONCAT( 'ie', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'uk%'
What is the

Code: Select all

CONCAT( 'ie', SUBSTRING ( romancode, 2 ) ) 
Part doing? IS that using SUBSTRING to concatentate it on, but replacing the first 2 chars?
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 I replace just the first 2 characters in all fiel

Post by simonmlewis »

It's thrown an error:

Code: Select all

UPDATE products SET romancode = CONCAT( 'uk', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'ie%'
#1630 - FUNCTION site_ie.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
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: How do I replace just the first 2 characters in all fiel

Post by Christopher »

simonmlewis wrote:It's thrown an error:

Code: Select all

UPDATE products SET romancode = CONCAT( 'uk', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'ie%'
#1630 - FUNCTION site_ie.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
Please just see the MySQL manual for the correct name of the function. Certainly the name requinix gave is clear enough to figure it out.
(#10850)
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I replace just the first 2 characters in all fiel

Post by requinix »

"SUBSTRING" has existed in MySQL forever. However what I've just learned is that it's possible for gratuitous whitespace to be a bad thing.

Function Name Parsing and Resolution
To use the name as a function call in an expression, there must be no whitespace between the name and the following “(” parenthesis character.
So

Code: Select all

UPDATE products SET romancode = CONCAT( 'uk', SUBSTRING( romancode, 2 ) ) WHERE romancode LIKE 'ie%'
/*                                                     ^                                          */
(I hadn't actually intended for the space to be there, incidentally)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How do I replace just the first 2 characters in all fiel

Post by Christopher »

I wonder if that is an SQL thing? It certainly would make parsing statements easier.
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I replace just the first 2 characters in all fiel

Post by simonmlewis »

That also puts an "e" in it. Changes one from ieTest123 to ukeTest123.
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 I replace just the first 2 characters in all fiel

Post by Celauran »

No it doesn't. It's leaving the e from ie because SUBSTRING isn't 0-indexed, so you need to start at 3 instead of 2.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I replace just the first 2 characters in all fiel

Post by simonmlewis »

Going on from this query, how do I query the database by asking it to look for a code, and taking out the first two characters within the query.

We have a UK site with a code such as TESTXL23.
But on the Ireland site this might be ieTESTXL23 or ukTEXTSL23.
So I want it to, on the fly, query the Ireland table, but ignoring the first two characters.

Is that possible?
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 I replace just the first 2 characters in all fiel

Post by Celauran »

Code: Select all

... WHERE somefield LIKE '%TESTXL23'
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I replace just the first 2 characters in all fiel

Post by requinix »

Celauran wrote:No it doesn't. It's leaving the e from ie because SUBSTRING isn't 0-indexed, so you need to start at 3 instead of 2.
:banghead:
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I replace just the first 2 characters in all fiel

Post by simonmlewis »

Ok bit of a problem.
Some products on the IE site, has words in front of the words found on UK that are not UK.
For example:
ukM47D1 (found on IE)
ukRECONM47D1 (also found on IE)

So it's producing two, but I only wanted the first one as on the UK site is is M47D1.

So can that query be altered so the first TWO ONLY characters are removed from the query, rather that doing a WildCard?
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 I replace just the first 2 characters in all fiel

Post by Celauran »

Sure. Just wrap the bit you want to match in a substring.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I replace just the first 2 characters in all fiel

Post by simonmlewis »

Pardon.....??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply