mysql: how to update replace all, case insensitive, text substrings

in this select statement,

SELECT pageid, title,
SUBSTRING(contents,
POSITION("one two three" IN contents) -0, 13) AS pageContents
FROM pagetable
WHERE contents LIKE '%one two three%' ;


‘%one two three%’ is case insensitive
however, the Position function used to display just that part of “contents” (as pageContents)
IS case sensitive and will return the front of the string, not the section you want, if the case is different.

This (with “lower”) works:

SELECT pageid, title,
SUBSTRING(contents,
POSITION(LOWER("one Two three") IN contents) -1, 15) AS pageContents
FROM pagetable
WHERE contents LIKE '%one Two three%' ;


this is a different workaround:

UPDATE pagetable
SET contents = REPLACE(contents, 'one-two three', 'Four Five Six'),
contents = REPLACE(contents, 'one-two-three', 'Four Five Six'),
contents = REPLACE(contents, 'one two-three', 'Four Five Six')
WHERE contents LIKE '%one-two three%'
OR contents LIKE '%one-two-three%'
OR contents LIKE '%one two-three%' ;