SAP HANA GUID conversion

Question

我如何从GUID_C22数据类型(16字节UUID 22)转换到GUID_X16 (UUID 16 raw)。我希望是这样的:

UUID22: 051Mf5p07jQZifdyH4x5}0  -> UUID16: 0050563C56401D36A348F362444845FC

我试过:hextoraw(UUID22)

Answer 1

uid22和uid16是SAP HANA上不存在的ABAP数据类型。因此，没有内置的转换功能可用。但是，您可能需要检查SCN，因为这个主题之前已经讨论过几次了。如视交叉上核将GUID_X16转换为GUID_C22

Answer 2

必须将创建的UUID的HEX表示分割成长度为3的片段，并将每个片段转换为只有数字、小写和大写拉丁字母以及左花括号和右花括号的两个字符片段。为此，我创建了一个包含两列hex:CHAR(3)和c22:CHAR(2)的转换表uuid_conv，其内容为:

hex c22
--- ---
000 00
001 01
...
03C 0y
03D 0z
03E 0{
03F 0}
...
FFB }x
FFC }y
FFD }z
FFE }{
FFF }}

使用您最喜欢的电子表格生成必要的内容。

之后我加入了如下内容:

with uuid as (
    select to_varchar(sysuuid) uuid from dummy
),
y as (
select uuid, substring(uuid,  1, 3) as ta,
         substring(uuid,  4, 3) as tb,
         substring(uuid,  7, 3) as tc,
         substring(uuid, 10, 3) as td,
         substring(uuid, 13, 3) as te,
         substring(uuid, 16, 3) as tf,
         substring(uuid, 19, 3) as tg,
         substring(uuid, 22, 3) as th,
         substring(uuid, 25, 3) as ti,
         substring(uuid, 28, 3) as tj,
         '0'||substring(uuid, 31, 2) as tk
from uuid )
select uuid, ck.c22 || cj.c22 || ci.c22 || ch.c22 || cg.c22 
             || cf.c22 || ce.c22 || cd.c22 || cc.c22 || cb.c22 || ca.c22
from y
inner join uuid_conv ca on (ca.hex = ta)
inner join uuid_conv cb on (cb.hex = tb)
inner join uuid_conv cc on (cc.hex = tc)
inner join uuid_conv cd on (cd.hex = td)
inner join uuid_conv ce on (ce.hex = te)
inner join uuid_conv cf on (cf.hex = tf)
inner join uuid_conv cg on (cg.hex = tg)
inner join uuid_conv ch on (ch.hex = th)
inner join uuid_conv ci on (ci.hex = ti)
inner join uuid_conv cj on (cj.hex = tj)
inner join uuid_conv ck on (ck.hex = tk)

可以考虑使用SQLSCRIPT函数的类似结构，并且会更好。