How to change the content?

Germaris · #1 (**permalink**) 11-12-06, 14:02

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:

Code:

<a href='mailto:"abc@domain.com"'>abc@domain.com</a>

This content is wrong.

It must be:

Code:

<a href="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,

Gerard

r937 · #2 (**permalink**) 11-12-06, 14:20

Code:

update daTable
   set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
 where notes like '%href=''mailto:"%">%'

Germaris · #3 (**permalink**) 11-12-06, 14:53

Quote:

Originally Posted by r937

Code:

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

r937 · #4 (**permalink**) 11-12-06, 16:54

did you try it? what results did it give?

Germaris · #5 (**permalink**) 11-12-06, 17:08

Yes, I tried... on a copy of the table!
After two attempts, this worked fine:

Code:

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

I thank you for your valuable help.

Best.

r937 · #6 (**permalink**) 11-12-06, 17:14

all you have done is remove the double quotes

in other words, you've changed this --

Code:

<a href='mailto:"abc@domain.com"'>abc@domain.com</a>

to this --

Code:

<a href='mailto:abc@domain.com'>abc@domain.com</a>

which is not what you asked

you asked for this --

Code:

<a href="mailto:abc@domain.com">abc@domain.com</a>

Germaris · #7 (**permalink**) 11-12-06, 17:26

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"! :-)

r937 · #8 (**permalink**) 11-12-06, 19:07

either single or double can be used

see http://www.w3.org/TR/html4/intro/sgm...tml#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

Germaris · #9 (**permalink**) 11-13-06, 07:58

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

Code:

<a href='mailto:abc@domain.com'>

with

Code:

<a href="mailto:abc@domain.com">

(Of course, this text isn't the only text contained in the field...)

I wrote:

Code:

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

This query has no effect!

What's wrong?

r937 · #10 (**permalink**) 11-13-06, 08:21

Code:

REPLACE(column_name, '''', '"')

Germaris · #11 (**permalink**) 11-13-06, 08:30

Still no effect !
And I perfectly know that more than a thousand rows must be affected...

I wrote:

Code:

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

r937 · #12 (**permalink**) 11-13-06, 08:51

you keep messing up in different places

Code:

WHERE column_name LIKE '%mailto:''%'

Germaris · #13 (**permalink**) 11-13-06, 09:09

Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".

I wrote:

Code:

UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';

...and nothing happens, right or wrong!

r937 · #14 (**permalink**) 11-13-06, 09:13

what do you get for this query:

Code:

select count(*) from table_name where column_name LIKE '%mailto:''%'

and what do you get for this query:

Code:

select count(*) from table_name where column_name LIKE '%mailto:"%'

this should tell you why

Germaris · #15 (**permalink**) 11-13-06, 09:31

Both queries return zero !!!!!

Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:

Code:

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:

Code:

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>

This is driving me quite mad... :-) GRRRR !!!

Internet Services

Web Development

Digital Marketing

Consumer Tech