Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: How to change the content?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update daTable
       set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
     where notes like '%href=''mailto:"%">%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try it? what results did it give?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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"! :-)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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?
    Last edited by Germaris; 11-13-06 at 09:05.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    REPLACE(column_name, '''', '"')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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:\'%';

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you keep messing up in different places
    Code:
    WHERE column_name LIKE '%mailto:''%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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!

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    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 !!!
    Last edited by Germaris; 11-13-06 at 10:36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •