Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
simonmlewis
DevNet Master
Posts: 4435 Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:
Post
by simonmlewis » Mon Apr 13, 2015 3:23 am
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.
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Mon Apr 13, 2015 4:13 am
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:
Post
by simonmlewis » Mon Apr 13, 2015 6:11 am
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:
Post
by simonmlewis » Mon Apr 13, 2015 6:12 am
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.
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Mon Apr 13, 2015 11:53 am
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)
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Mon Apr 13, 2015 2:48 pm
"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)
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Mon Apr 13, 2015 3:56 pm
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:
Post
by simonmlewis » Tue Apr 14, 2015 2:59 am
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.
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Tue Apr 14, 2015 6:13 am
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:
Post
by simonmlewis » Tue Apr 14, 2015 8:34 am
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.
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Tue Apr 14, 2015 8:40 am
Code: Select all
... WHERE somefield LIKE '%TESTXL23'
requinix
Spammer :|
Posts: 6617 Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA
Post
by requinix » Tue Apr 14, 2015 12:25 pm
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.
simonmlewis
DevNet Master
Posts: 4435 Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:
Post
by simonmlewis » Wed Apr 15, 2015 4:30 am
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.
Celauran
Moderator
Posts: 6427 Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada
Post
by Celauran » Wed Apr 15, 2015 6:08 am
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:
Post
by simonmlewis » Wed Apr 15, 2015 2:36 pm
Pardon.....??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.