case insensitive REPLACE(...)?

[Simon]

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]

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

--
______
laqula

U¿ytkownik "Simon" <spambucket@example.com> napisa³ w wiadomo¶ci
news:47fee5Fff9a2U1@individual.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
>

[Bill Karwin]

"Simon" <spambucket@example.com> wrote in message
news:47fee5Fff9a2U1@individual.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.

[Simon]

"laqula" <laqula@wp.pl> wrote in message
news:duv0rd$25b4$1@news2.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]

>

> 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

[Bill Karwin]

"Simon" <spambucket@example.com> wrote in message
news:47q3uvFg6t31U2@individual.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.