Hi there !
I'm quite desperate as I can't find a quick method to partially change the content of a field.
I explain:
In a field named 'notes', PART of the content is:
<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>
This content is wrong.
It must be:
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>
How can I make the change with a SQL Query instead of manually make the change row by row (approximately 1500 rows contain the error!!!) considering the fact that abc@.domain.com is different in each of the rows?
Many thanks in advance for any help!
Best regards,
Gerardupdate daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'|||update daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'
Hi !
Thank you for replying. I appreciate.
But, as I'm quite old and learning, I want to really understand the syntax I'll write.
Some questions arise:
1 - Why have we "replace" repeated twice?
2 - The single and double quotes you wrote in your example doesn't correspond to those I put in my example.
3 - What will happen to the email addresses which are differents?
Be sure, professor, I certainly do not want to bother you! :-)
Best regards,
Gerard|||did you try it? what results did it give?|||Yes, I tried... on a copy of the table!
After two attempts, this worked fine:
UPDATE `table_name` SET column_name = REPLACE(column_name, '"', '') WHERE column_name LIKE '%href=\'mailto:"%';
I thank you for your valuable help.
Best.|||all you have done is remove the double quotes
in other words, you've changed this --<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>to this --<a href='mailto:abc@.domain.com'>abc@.domain.com</a>which is not what you asked
you asked for this --<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>|||YES you are right about the kind of quotes but the principle remained the same: remove the extra quotes just before and after the address, no matter if the quotes are single or double. I opted for the single quotes which are more "orthodox"! :-)|||either single or double can be used
see http://www.w3.org/TR/html4/intro/sgmltut.html#attributes
if you look at all the web sites out there (okay, just look at some, you don't really have to look at all of them) you will find that most of them use double quotes
also, the incidence of a single quote required inside a double-quoted string is more frequent than a double quote inside a single-quoted string
:)|||You are right.
I have read about attributes in the page you mentioned.
And I tried to follow your advice and to replace the single quotes with double quotes.
For example, I tried to replace where it is necessary
<a href='mailto:abc@.domain.com'>
with
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">
(Of course, this text isn't the only text contained in the field...)
I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '', "") WHERE column_name LIKE '%mailto:\'%';
This query has no effect!
What's wrong?|||REPLACE(column_name, '''', '"')|||Still no effect !
And I perfectly know that more than a thousand rows must be affected...
I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:\'%';|||you keep messing up in different places
WHERE column_name LIKE '%mailto:''%'|||Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".
I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';
...and nothing happens, right or wrong!|||what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:''%'and what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:"%'this should tell you why
:)|||Both queries return zero !!!!!
Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:
Autres adresses de courriel : <a href='mailto:jr.reverte@.laposte.fr'><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href='mailto:entsoa23@.fr.st'><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>
and I want to change it to:
Autres adresses de courriel : <a href="http://links.10026.com/?link=mailto:jr.reverte@.laposte.fr"><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href="http://links.10026.com/?link=mailto:entsoa23@.fr.st"><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>
This is driving me quite mad... :-) GRRRR !!!|||i believe you are not copying my exact code, but rather typing it in yourself, and making errors while typing
you must make very sure that you understand the difference between two single quotes in a row:''and a doublequote:"|||Obviously, yes. I know the difference.
And I copied the exact code you posted.
But I found one error.
It isn't
'%mailto:\'%'
but
'%\'mailto:%'
we are looking for.
The position of the quote was wrong.
And this change makes the query returning a number!
Using this change, I unsuccessfully (no row affected) tried to apply the modifying query this way (real names of table and column):
UPDATE `_annuairecopy` SET notes = REPLACE ( notes, '', "") WHERE notes LIKE 'href=%\'mailto:%\'%';
(please report to the REAL content of field I previously posted)|||please stop using those horrible backslashes!!!
UPDATE `_annuairecopy`
SET notes = REPLACE ( notes, '''', '"')
WHERE notes LIKE '%href=''mailto:%'|||Yes indeed, they are horrible.
But I was so tired to get error messages each time I used an even number of single quotes without a backslash!
The query has worked fine !!!
Could you imagine how happy I am?
I thank you for your patience and all the time you spent helping me.
Best from Gerard.
