oracle plsql 转义字符,使用Unicode转义负载Oracle PLSQL生成HMAC SHA1

最新推荐文章于 2022-04-24 17:06:01 发布

转载最新推荐文章于 2022-04-24 17:06:01 发布 · 677 阅读

文章标签：

#oracle plsql 转义字符

本文介绍了一种使用ASCIISTR和REGEXP_REPLACE函数处理字符串的方法，通过创建复杂的正则表达式模式实现对Unicode字符的转换，并提供了一个PL/SQL函数实现示例。

从@wernfried domscheit的使用方法开始

ASCIISTR

和

regexp_replace

,它可以扩展到整个替换列表以获取小写版本:

with sample(str) as (

select 'Ã¤Ã¶Ã¥ ab\ABC' from dual

), patterns(ord, pat, rep) as (

select 1, '\\A([[:xdigit:]]{3})', '\\a\1' from dual union all

select 2, '\\B([[:xdigit:]]{3})', '\\b\1' from dual union all

select 3, '\\C([[:xdigit:]]{3})', '\\c\1' from dual union all

select 4, '\\D([[:xdigit:]]{3})', '\\d\1' from dual union all

select 5, '\\E([[:xdigit:]]{3})', '\\e\1' from dual union all

select 6, '\\F([[:xdigit:]]{3})', '\\f\1' from dual union all

select 7, '\\([[:xdigit:]])A([[:xdigit:]]{2})', '\\\1a\2' from dual union all

select 8, '\\([[:xdigit:]])B([[:xdigit:]]{2})', '\\\1b\2' from dual union all

select 9, '\\([[:xdigit:]])C([[:xdigit:]]{2})', '\\\1c\2' from dual union all

select 10, '\\([[:xdigit:]])D([[:xdigit:]]{2})', '\\\1d\2' from dual union all

select 11, '\\([[:xdigit:]])E([[:xdigit:]]{2})', '\\\1e\2' from dual union all

select 12, '\\([[:xdigit:]])F([[:xdigit:]]{2})', '\\\1f\2' from dual union all

select 13, '\\([[:xdigit:]]{2})A([[:xdigit:]])', '\\\1a\2' from dual union all

select 14, '\\([[:xdigit:]]{2})B([[:xdigit:]])', '\\\1b\2' from dual union all

select 15, '\\([[:xdigit:]]{2})C([[:xdigit:]])', '\\\1c\2' from dual union all

select 16, '\\([[:xdigit:]]{2})D([[:xdigit:]])', '\\\1d\2' from dual union all

select 17, '\\([[:xdigit:]]{2})E([[:xdigit:]])', '\\\1e\2' from dual union all

select 18, '\\([[:xdigit:]]{2})F([[:xdigit:]])', '\\\1f\2' from dual union all

select 19, '\\([[:xdigit:]]{3})A', '\\\1a' from dual union all

select 20, '\\([[:xdigit:]]{3})B', '\\\1b' from dual union all

select 21, '\\([[:xdigit:]]{3})C', '\\\1c' from dual union all

select 22, '\\([[:xdigit:]]{3})D', '\\\1d' from dual union all

select 23, '\\([[:xdigit:]]{3})E', '\\\1e' from dual union all

select 24, '\\([[:xdigit:]]{3})F', '\\\1f' from dual union all

select 25, '\\([[:xdigit:]]{4})', '\u\1' from dual union all

select 26, '\\u005c', '\\\\' from dual

), recur(ord, str, tr) as (

select ord, str

, REGEXP_REPLACE(asciistr(str), pat, rep)

from sample

join patterns

on ord = 1

union all

select recur.ord+1, str

, REGEXP_REPLACE(tr, pat, rep)

from recur

join patterns

on patterns.ord = recur.ord + 1

)

select * from recur where ord = 26;

如果您希望使用pl/sql函数来完成此任务,则可以将一系列转换嵌套起来并放入一个函数中。如果不希望最终的转换为只删除外部regexp\u replace,请执行以下操作:

create or replace function Escape_Unicode(pCLOB clob) RETURN clob is

BEGIN

return regexp_replace(

regexp_replace(

asciistr(pClOB)

, '\\A([[:xdigit:]]{3})'

, '\\a\1'

)

, '\\B([[:xdigit:]]{3})'

, '\\b\1'

)

, '\\C([[:xdigit:]]{3})'

, '\\c\1'

)

, '\\D([[:xdigit:]]{3})'

, '\\d\1'

)

, '\\E([[:xdigit:]]{3})'

, '\\e\1'

)

, '\\F([[:xdigit:]]{3})'

, '\\f\1'

)

, '\\([[:xdigit:]])A([[:xdigit:]]{2})'

, '\\\1a\2'

)

, '\\([[:xdigit:]])B([[:xdigit:]]{2})'

, '\\\1b\2'

)

, '\\([[:xdigit:]])C([[:xdigit:]]{2})'

, '\\\1c\2'

)

, '\\([[:xdigit:]])D([[:xdigit:]]{2})'

, '\\\1d\2'

)

, '\\([[:xdigit:]])E([[:xdigit:]]{2})'

, '\\\1e\2'

)

, '\\([[:xdigit:]])F([[:xdigit:]]{2})'

, '\\\1f\2'

)

, '\\([[:xdigit:]]{2})A([[:xdigit:]])'

, '\\\1a\2'

)

, '\\([[:xdigit:]]{2})B([[:xdigit:]])'

, '\\\1b\2'

)

, '\\([[:xdigit:]]{2})C([[:xdigit:]])'

, '\\\1c\2'

)

, '\\([[:xdigit:]]{2})D([[:xdigit:]])'

, '\\\1d\2'

)

, '\\([[:xdigit:]]{2})E([[:xdigit:]])'

, '\\\1e\2'

)

, '\\([[:xdigit:]]{2})F([[:xdigit:]])'

, '\\\1f\2'

)

, '\\([[:xdigit:]]{3})A'

, '\\\1a'

)

, '\\([[:xdigit:]]{3})B'

, '\\\1b'

)

, '\\([[:xdigit:]]{3})C'

, '\\\1c'

)

, '\\([[:xdigit:]]{3})D'

, '\\\1d'

)

, '\\([[:xdigit:]]{3})E'

, '\\\1e'

)

, '\\([[:xdigit:]]{3})F'

, '\\\1f'

)

, '\\([[:xdigit:]]{4})'

, '\u\1'

)

, '\\u005c'

, '\\\\'

);

end;

select escape_unicode('Ã¤Ã¶Ã¥ ab\ABCd') from dual;