HANA DB 中文排序问题
HANA DB 表中的中文数据存到表里是以16进制Hexadecimal保存的,所以对字段排序时结果看起来是乱的。
解决办法就是创建一个function先转拼音再排序
CREATE FUNCTION GETHZPY
(
in str varchar(300)
)
RETURNS
c NVARCHAR(300)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
chh NVARCHAR(300);
chn NVARCHAR(300);
chs NVARCHAR(300);
BEGIN
c:='';
WHILE (LENGTH(str)>0) DO
--依次取单个字符 GB2312 区位编码,
chh := LEFT(str,1);
chn := STRTOBIN(LEFT(str,1),'GB2312') ;
--汉字字符,返回字符对应首字母,非汉字字符,返回原字符
IF(Least(chn,'B0A1')='B0A1' AND Greatest(chn,'D7F9')='D7F9')
THEN
select
case when Least(chn,'B0A1') = 'B0A1' then
(
case when Greatest(chn,'B0C4') = 'B0C4' then 'A'
else case when Greatest(chn,'B2C0') = 'B2C0' then 'B'
else case when Greatest(chn,'B4ED') = 'B4ED' then 'C'
else case when Greatest(chn,'B6E9') = 'B6E9' then 'D'
else case when Greatest(chn,'B7A1') = 'B7A1' then 'E'
else case when Greatest(chn,'B8C0') = 'B8C0' then 'F'
else case when Greatest(chn,'B9FD') = 'B9FD' then 'G'
else case when Greatest(chn,'BBF6') = 'BBF6' then 'H'
else case when Greatest(chn,'BFA5') = 'BFA5' then 'J'
else case when Greatest(chn,'C0AB') = 'C0AB' then 'K'
else case when Greatest(chn,'C2E7') = 'C2E7' then 'L'
else case when Greatest(chn,'C4C2') = 'C4C2' then 'M'
else case when Greatest(chn,'C5B5') = 'C5B5' then 'N'
else case when Greatest(chn,'C5BD') = 'C5BD' then 'O'
else case when Greatest(chn,'C6D9') = 'C6D9' then 'P'
else case when Greatest(chn,'C8BA') = 'C8BA' then 'Q'
else case when Greatest(chn,'C8F5') = 'C8F5' then 'R'
else case when Greatest(chn,'CBF9') = 'CBF9' then 'S'
else case when Greatest(chn,'CDD9') = 'CDD9' then 'T'
else case when Greatest(chn,'CEF4') = 'CEF4' then 'W'
else case when Greatest(chn,'D1B8') = 'D1B8' then 'X'
else case when Greatest(chn,'D4D0') = 'D4D0' then 'Y'
else 'Z'
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end )
else ' ' end
INTO chs FROM DUMMY;
ELSE
chs:=UPPER(chh);
END IF;
str:=RIGHT(str,LENGTH(str)-1);
c:=c||chs ;
END WHILE;
END;
引用HANA函数:
STRTOBIN:将字符串中所有字符转换为 特定字符集对应的区位编码。STRTOBIN(‘字符串’,‘字符集’)。
LEAST:将行多个参数值中的最小值返回;LEAST(‘参数1’,‘参数2’,…,‘参数n’)。
GREATEST:将行多个参数值中的最大值返回;GREATEST(‘参数1’,‘参数2’,…,‘参数n’)。
UPPER:将传递的字符串中的所有字符转换为大写;UPPER(‘字符串’)。
参考:
https://blog.csdn.net/i042416/article/details/88529634
https://www.jianshu.com/p/7ec3ada9da54
post SAP-Garson
原文链接:https://blog.csdn.net/xiayutian_c/article/details/120866564文章来自于网络,如果侵犯了您的权益,请联系站长删除!