Collate Latin1_General_CI_AI equivalent in DB2

Question

Regardless of the collation of the column or the entire db, in SQLServer I wrote Update MyTable Set MyColumn = Replace(MyColumn Collate Latin1_General_CI_AI, 'foo', 'bar') to replace FOO, Foo or foo with bar, while the case of the rest remains unchanged.

Regardless of the code page of the column or the entire db, how do the same with DB2? I tryed this:

Update MyTable Set MyColumn = Replace(COLLATION_KEY_BIT(MyColumn, 'CLDR2701_EX_S1'), 'foo', 'bar')

I have to execute that query regularly at various customers, with DB2 LUW 10.5 and iSeries 7.1, 7.2, 7.3. I think all of them have Codepage 1252.

But I'm not secure is the right way. First of all, DB2 says that CLDR2701_XE_S1 is undefined. Furthermore, I think that the function transforms the string each letter with the first with the same value (but until I find an existing collation name I'll never know)

Thank you

Lucio

Answer 1

Something like this for DB2 for LUW 10.5:

SELECT
  XMLCAST (XMLQUERY ('fn:replace ($s, "foo", "bar", "i")' PASSING str AS "s") AS VARCHAR(100))
  AS S
FROM 
(
  VALUES 
    'abc Foo def'
  , 'abc fOO def'
) mytab (str) 

S
abc bar def
abc bar def

fiddle

Try REGEXP_REPLACE for DB2 for IBM i.