Professional Web Applications Themes

case insensitive REPLACE(...)? - MySQL

Hi, I need to search and replace a web address, but the original address could be in any case. Is there a way of doing a case insensitive search a replace? Using LOWER(...) would not work as the rest of the text needs to keep its case. Many thanks Simon...

  1. #1

    Default case insensitive REPLACE(...)?

    Hi,

    I need to search and replace a web address, but the original address could
    be in any case.
    Is there a way of doing a case insensitive search a replace?

    Using LOWER(...) would not work as the rest of the text needs to keep its
    case.

    Many thanks

    Simon


    Simon Guest

  2. #2

    Default Re: case insensitive REPLACE(...)?

    Use LOWER() on database value and once again LOWER() on searching value.

    --
    ______
    laqula

    Użytkownik "Simon" <spambucketexample.com> napisał w wiadomości
    news:47fee5Fff9a2U1individual.net...
    > Hi,
    >
    > I need to search and replace a web address, but the original address could
    > be in any case.
    > Is there a way of doing a case insensitive search a replace?
    >
    > Using LOWER(...) would not work as the rest of the text needs to keep its
    > case.
    >
    > Many thanks
    >
    > Simon
    >

    laqula Guest

  3. #3

    Default Re: case insensitive REPLACE(...)?

    "Simon" <spambucketexample.com> wrote in message
    news:47fee5Fff9a2U1individual.net...
    > Hi,
    >
    > I need to search and replace a web address, but the original address could
    > be in any case.
    > Is there a way of doing a case insensitive search a replace?
    Test the position of your substring using INSTR() against a LOWER()ed
    version of the string. This gives you the position of the substring. Then
    use that within an INSERT() function (not the INSERT statement) to replace
    the substring.

    INSERT(textField, INSTR(LOWER(textField), LOWER(oldUrl)), LENGTH(oldUrl),
    newUrl)

    See INSERT() and INSTR() docs on this page:
    [url]http://dev.mysql.com/doc/refman/5.0/en/string-functions.html[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: case insensitive REPLACE(...)?


    "laqula" <laqulawp.pl> wrote in message
    news:duv0rd$25b4$1news2.ipartners.pl...
    > Use LOWER() on database value and once again LOWER() on searching value.
    >
    As I mentioned, that will not work as using LOWER will change the case of
    the whole database value.
    I want to keep the original case of the rest of the database value.

    Simon


    Simon Guest

  5. #5

    Default Re: case insensitive REPLACE(...)?

    >
    > Test the position of your substring using INSTR() against a LOWER()ed
    > version of the string. This gives you the position of the substring.
    > Then use that within an INSERT() function (not the INSERT statement) to
    > replace the substring.
    >
    > INSERT(textField, INSTR(LOWER(textField), LOWER(oldUrl)), LENGTH(oldUrl),
    > newUrl)
    >
    > See INSERT() and INSTR() docs on this page:
    > [url]http://dev.mysql.com/doc/refman/5.0/en/string-functions.html[/url]
    >
    Hum, that works great, but the problem is that I might have more that one
    entry in the textField.
    Is there a way of doing a recurring INSERT(...) in one UPDATE statement?

    Many thanks

    Simon


    Simon Guest

  6. #6

    Default Re: case insensitive REPLACE(...)?

    "Simon" <spambucketexample.com> wrote in message
    news:47q3uvFg6t31U2individual.net...
    > Hum, that works great, but the problem is that I might have more that one
    > entry in the textField.
    > Is there a way of doing a recurring INSERT(...) in one UPDATE statement?

    I don't think so. REPLACE() does that, but not INSERT(). You can nest
    them, but that's not recurring.

    Not every task is best done in a single SQL statement. You may have to
    write application code.

    Another suggestion is that if you need to do this a single time (instead of
    automated), generate a series of UPDATE statements:

    SELECT CONCAT(
    'UPDATE myTable SET textField = \'',
    REPLACE(textField, '\'', '\'\''),
    '\' WHERE primaryKey = ', primaryKey, ';')
    FROM myTable
    WHERE INSTR(LOWER(textField), LOWER(oldUrl)) > 0

    Then hand-edit the output with a text editor, which should be able to do
    case-insensitive global search & replace very easily. Then run the update
    statements as a script.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  2. [PHP] case insensitive sort
    By Steve Buehler in forum PHP Development
    Replies: 0
    Last Post: August 26th, 03:52 PM
  3. hash key of %ENV is case insensitive (Win32)
    By Tassilo v. Parseval in forum PERL Miscellaneous
    Replies: 5
    Last Post: August 18th, 03:02 AM
  4. Case-insensitive str_replace
    By -= Patrick =- in forum PHP Development
    Replies: 1
    Last Post: August 6th, 06:29 PM
  5. case-insensitive sort
    By Andrey Voronov in forum Oracle Server
    Replies: 3
    Last Post: January 13th, 04:24 PM

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