NCHR for db2 LUW (new scalar function)

Oracle supports NCHR function. To migrate oracle application, we need this scalar function.

NCHR returns the character having the binary equivalent to number in the national character set. The value returned is always NVARCHAR2. This function is equivalent to using the CHR function with the USING NCHAR_CS clause.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions091.htm

The existing applications use this function frequently.

SELECT station_name FROM t_subway WHERE station_name between NCHR(48148) AND NCHR(49323) ;

(48148 and 49323 represent the unicode decimal numbers, 48148 -> '바' , 49323 -> '삫' )

https://www.key-shortcut.com/en/writing-systems/%ED%95%9C%EA%B5%AD-korean-script/hangul-characters-3/

Post comment

Guest

Reply
| May 8, 2019

This function can be written in a unicode database as a simple UDF:

-- Convert integer value to unicode character
-- Integer value represents UTF-32 Unicode codepoint, e.g.:
--   A = x41    = 65
--   Ł = x0141 = 321 (L with stroke)
--   𝄞 = x1d11e = 119070 (G-clef)
create or replace function nchr( i integer )
returns char(1 codeunits32)
language sql
begin
declare result varchar(1 codeunits32); --
declare stmt varchar(128); --
declare hexValue varchar(8); --
declare C cursor for S; --

set hexValue = hex(i); --
-- Handle endianness
if substr(hex(1),2,1) = '1' then
    -- little-endian
    set hexValue = substr(hexValue,5,2) || substr(hexValue,3,2) || substr(hexValue,1,2); --
else
    -- big-endian
    set hexValue = substr(hexValue,3,6); --
end if; --

set stmt = 'values U&''\+' || hexValue || ''''; --
prepare S from stmt; --
open C; --
fetch from C into result; --
close c; --
return result; --
end;

0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

NCHR for db2 LUW (new scalar function)