PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.		  
Loading
It is currently Thu Jun 20, 2019 11:24 am

View unanswered posts | View active topics


Board index » System » Databases

All times are UTC - 5 hours




Post new topic Reply to topic  Page 1 of 1
  [ 15 posts ] 
  Print view Previous topic | Next topic 
Author Message
simonmlewis
PostPosted: Mon Apr 13, 2015 3:23 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
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:

Syntax: [ Download ] [ Hide ]
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.
Top
 Profile  
 
requinix
PostPosted: Mon Apr 13, 2015 4:13 am 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
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...
Quote:
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:
UPDATE products SET romancode = CONCAT( 'ie', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'uk%'
Top
 Profile  
 
simonmlewis
PostPosted: Mon Apr 13, 2015 6:11 am 
Offline
DevNet Master

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

What is the
Syntax: [ Download ] [ Hide ]
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.
Top
 Profile  
 
simonmlewis
PostPosted: Mon Apr 13, 2015 6:12 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
It's thrown an error:

Syntax: [ Download ] [ Hide ]
UPDATE products SET romancode = CONCAT( 'uk', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'ie%'

Quote:
#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.
Top
 Profile  
 
Christopher
PostPosted: Mon Apr 13, 2015 11:53 am 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
simonmlewis wrote:
It's thrown an error:

Syntax: [ Download ] [ Hide ]
UPDATE products SET romancode = CONCAT( 'uk', SUBSTRING ( romancode, 2 ) ) WHERE romancode LIKE 'ie%'

Quote:
#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)
Top
 Profile  
 
requinix
PostPosted: Mon Apr 13, 2015 2:48 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
"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
Quote:
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:
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)
Top
 Profile  
 
Christopher
PostPosted: Mon Apr 13, 2015 3:56 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
I wonder if that is an SQL thing? It certainly would make parsing statements easier.

_________________
(#10850)
Top
 Profile  
 
simonmlewis
PostPosted: Tue Apr 14, 2015 2:59 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
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.
Top
 Profile  
 
Celauran
PostPosted: Tue Apr 14, 2015 6:13 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
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.

_________________
Supported PHP versions No longer supported versions
Top
 Profile  
 
simonmlewis
PostPosted: Tue Apr 14, 2015 8:34 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
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.
Top
 Profile  
 
Celauran
PostPosted: Tue Apr 14, 2015 8:40 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Syntax: [ Download ] [ Hide ]
... WHERE somefield LIKE '%TESTXL23'

_________________
Supported PHP versions No longer supported versions
Top
 Profile  
 
requinix
PostPosted: Tue Apr 14, 2015 12:25 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6617
Location: WA, USA
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:
Top
 Profile  
 
simonmlewis
PostPosted: Wed Apr 15, 2015 4:30 am 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
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.
Top
 Profile  
 
Celauran
PostPosted: Wed Apr 15, 2015 6:08 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6425
Location: Montreal, Canada
Sure. Just wrap the bit you want to match in a substring.

_________________
Supported PHP versions No longer supported versions
Top
 Profile  
 
simonmlewis
PostPosted: Wed Apr 15, 2015 2:36 pm 
Offline
DevNet Master

Joined: Wed Oct 08, 2008 3:39 pm
Posts: 4434
Location: United Kingdom
Pardon.....??

_________________
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 15 posts ] 

Board index » System » Databases

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group