How to change the content?

[Germaris]

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:"[e-mail]"'>[e-mail]</a>

This content is wrong.

It must be:

<a href="mailto:[e-mail]">[e-mail]</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 [e-mail] is different in each of the rows?

Many thanks in advance for any help!

Best regards,

Gerard

[Installer]

I don't see why this wouldn't work, although you might have to play with the quoting and escaping:

SELECT REPLACE(REPLACE(notes, '"', ''), "'", '"') AS altered_notes

[Germaris]

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 don't correspond to those I put in my example. Particularly, I am questioning my self about the double quotes which are positioned just after the : in the present version and which must disappear in the modified version.
3 - What will happen to the email addresses which are differents?
4 - What will happen if in the other parts of the content there are other quotes (single or double)? Will they also be changed or left intact?

Be sure, I certainly do not want to bother you! :-)
Just trying to understand well...

Best regards,

Gerard

PS: In my next reply, I'll submit to you a full query wrote following your advices.

[NogDog]

To avoid having to construct a regex for this, I'd leave the single quotes around the entire expression, and try something like the following to delete the double quotes:

UPDATE table_name SET notes = REPLACE(notes, '"', '') WHERE notes LIKE '%href=\'mailto:"%';

[Germaris]

NogDog wrote:

UPDATE table_name SET notes = REPLACE(notes, '"', '') WHERE notes LIKE '%href=\'mailto:"%';

VERY GOOD! IT PERFECTLY WORKS!

Dear Professor, would you please tell me if I understand well the syntax of your Query?
In the part of it which is between the brackets, after the first comma you wrote the quotes as they are and after the second comma you wrote them as they must be.
Right?

Thousands of thanks!

[Installer]

Click here for the MySQL String Functions manual page; scroll down to find REPLACE().

[Germaris]

Thanks !!!