Page 1 of 1

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

Posted: Mon Apr 13, 2015 3:23 am
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 ) ) ) 

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

Posted: Mon Apr 13, 2015 4:13 am
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%'

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

Posted: Mon Apr 13, 2015 6:11 am
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?

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

Posted: Mon Apr 13, 2015 6:12 am
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

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

Posted: Mon Apr 13, 2015 11:53 am
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.

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

Posted: Mon Apr 13, 2015 2:48 pm
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)

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

Posted: Mon Apr 13, 2015 3:56 pm
by Christopher
I wonder if that is an SQL thing? It certainly would make parsing statements easier.

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

Posted: Tue Apr 14, 2015 2:59 am
by simonmlewis
That also puts an "e" in it. Changes one from ieTest123 to ukeTest123.

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

Posted: Tue Apr 14, 2015 6:13 am
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.

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

Posted: Tue Apr 14, 2015 8:34 am
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?

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

Posted: Tue Apr 14, 2015 8:40 am
by Celauran

Code: Select all

... WHERE somefield LIKE '%TESTXL23'

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

Posted: Tue Apr 14, 2015 12:25 pm
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:

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

Posted: Wed Apr 15, 2015 4:30 am
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?

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

Posted: Wed Apr 15, 2015 6:08 am
by Celauran
Sure. Just wrap the bit you want to match in a substring.

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

Posted: Wed Apr 15, 2015 2:36 pm
by simonmlewis
Pardon.....??