Change replace System-defined in db2 luw to consider NLS characters in search.

Wrong result when executing the replace function with certain characters in the codeset euc-kr DB. (It comes out fine for utf-8 DB, but not for euc-kr.)

Case No : TS012082536

*L3 team comment*

#####################################################

Our documentation clearly points out that for replace function, search doesn't consider NLS character. So please request customer to open AHA request.

-REPLACE scalar function-

https://www.ibm.com/docs/en/db2/11.5?topic=functions-replace

If the search string is not found in the source string, the search string is returned unchanged. If the Unicode database is defined with a locale-sensitive UCA-based collation and none of the source-string, search-string, or replace-string arguments are defined as FOR BIT DATA or as a binary string, a linguistically correct search is done. Otherwise, the search is done using a binary comparison with no special consideration for multi-byte characters.

In example, check out hex value of example case in UTF-8 database and database with codepage 970. The codepoints (hex/binary) used by different codepages to represent these characters, is the reason of the difference between the result.

Varchar type:

Now look at customer non-working case hex string, Korean database (codepage 970, double bye database).

REPLACE(source_string, search_string, replace_string )

Source Sting hex value

X'bfc0c0c0bdc9'

Search string:

X'c0c0'      

As Db2 works on binary comparison, it will replace the c0c0 and move to the next comparison. So it findout 2nd byte c0 and 3rd byte c0, and make the replace and move to the next byte.

result:

X'BF78C0BDC9' ==>78 is varchar for 'x' This lead  the issue you pointed out.

UTF-8 Strings in the example case, has no such issue, source and search strings:

0xEC98A4EC9D91EC8BAC

0xEC9D91
#####################################################

[ref1]

##############################################################

REPLACE(source_string, search_string, replace_string )

Source Sting hex value

X'bfc0c0c0bdc9'

Search string:

X'c0c0'      

X'BF78C0BDC9' ==>78 is varchar for 'x'

##############################################################

[ref2,Workaround]

###############################################################

-GRAPHIC scalar function-

https://www.ibm.com/docs/en/db2/11.5?topic=functions-graphic

#1

$ db2 connect to eucdb2

  Database Connection Information

Database server        = DB2/LINUXX8664 11.5.5.0

SQL authorization ID   = DB2INST1

Local database alias   = EUCDB2

$ db2 "SELECT replace(vargraphic('오응심'),vargraphic('응'),vargraphic('*')) FROM SYSIBM.SYSDUMMY1"

1

------------

오＊심

 1 record(s) selected.

$ db2 "SELECT hex(replace(vargraphic('오응심'),vargraphic('응'),vargraphic('*'))) FROM SYSIBM.SYSDUMMY1"

1

------------------------

BFC0A3AABDC9

 1 record(s) selected.

#2

OR

create table tg2 ( c1 int, c2 graphic(10), c3 graphic(1),c4 graphic(1) );

insert into tg2 values (1, x'bfc0c0c0bdc9', x'c0c0', 'x');

select hex(c2), hex(c3),hex(c4) from tg2

1                                        2    3  

---------------------------------------- ---- ----

BFC0C0C0BDC9A1A1A1A1A1A1A1A1A1A1A1A1A1A1 C0C0 A3F8

select replace (c2,c3,c4) from tg2

1

----------------------------------------

오ｘ심                　

select hex(replace (c2,c3,c4)) from tg2

1                                      

----------------------------------------

BFC0A3F8BDC9A1A1A1A1A1A1A1A1A1A1A1A1A1A1

###############################################################