Professional Web Applications Themes

replace query - MySQL

I been playing with this for awhile, but can't seem to successfully execute a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2 The table only contains around 650 records, yet the query just hangs. I came across this syntax example: update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]'); My real world usage is: UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\'); where the backslash is added after the 4-digit year. An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The column is defined as varchar(50). Any suggestions would be greatly appreciated....

  1. #1

    Default replace query

    I been playing with this for awhile, but can't seem to successfully execute
    a simple replace query with MySQL 4.1.12 via phpMyAdmin 2.7.0-pl2

    The table only contains around 650 records, yet the query just hangs.

    I came across this syntax example:

    update [table_name] set [field_name] =
    replace([field_name],'[string_to_find]','[string_to_replace]');

    My real world usage is:

    UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\');

    where the backslash is added after the 4-digit year.

    An example piece of fileloc data looks like "2001DOM-MK008-C.PDF". The
    column is defined as varchar(50).

    Any suggestions would be greatly appreciated.


    Bosconian Guest

  2. #2

    Default Re: replace query

    On 8 Feb, 03:38, "Bosconian" <com> wrote: 

    Try doubling up your backslash thus:
    UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\\');

    Captain Guest

  3. #3

    Default Re: replace query


    "Bosconian" <com> wrote in message
    news:com... 

    You're getting into a neverending loop

    2001DOM-MK008-C.PDF becomes 2001\DOM-MK008-C.PDF
    2001\DOM-MK008-C.PDF becomes 2001\\DOM-MK008-C.PDF
    2001\\DOM-MK008-C.PDF becomes 2001\\\DOM-MK008-C.PDF

    Try :

    UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','test\');

    followed by

    UPDATE attachments SET fileloc = REPLACE(fileloc,'test\','2001\');



    Sean Guest

  4. #4

    Default Re: replace query

    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    >
    > Try doubling up your backslash thus:
    > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','2001\\');
    >[/ref]

    Yup, escaping the backslash did the trick. Shame on me for not thinking of
    it on my own.

    Thanks!


    Bosconian Guest

  5. #5

    Default Re: replace query

    "Sean" <sean.anderson[nospam]oakleafgroup.biz> wrote in message
    news:skynet.co.uk... 
    >
    > You're getting into a neverending loop
    >
    > 2001DOM-MK008-C.PDF becomes 2001\DOM-MK008-C.PDF
    > 2001\DOM-MK008-C.PDF becomes 2001\\DOM-MK008-C.PDF
    > 2001\\DOM-MK008-C.PDF becomes 2001\\\DOM-MK008-C.PDF
    >
    > Try :
    >
    > UPDATE attachments SET fileloc = REPLACE(fileloc,'2001','test\');
    >
    > followed by
    >
    > UPDATE attachments SET fileloc = REPLACE(fileloc,'test\','2001\');
    >
    >
    >[/ref]

    You might be right about the endless loop although no data ever changed
    (probably because the query never finished.) The solution in this case
    though was to simply escape the backslash.


    Bosconian Guest

Similar Threads

  1. help with the replace(pattern, replace)
    By Cloudesk in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 24th, 03:22 PM
  2. Replace string query - double trouble!
    By Gary@garywhittle.co.uk in forum MySQL
    Replies: 2
    Last Post: November 24th, 07:52 PM
  3. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  4. Search and replace (super global replace)
    By johnweiffenbach@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 8th, 08:56 AM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139