不使用REGEXP_REPLACE和PL/SQL格式化UUID字符串 [英] Formating UUID String without REGEXP_REPLACE and PL/SQL

问题描述

我想格式化sys_guid()函数的结果格式，例如

I'd like to format the result of the sys_guid() function such as proposed in this answer

select regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5') 
         as FORMATTED_GUID 
 from dual

出于性能原因，我想避免使用regexp_replace(因为我处理了大量记录).

From performance reasons I'd like to avoid the usage of regexp_replace (as I process large number of records).

我的情况可以简化为该用例:

My scenario can be simplified to this use case:

 select rawtohex(sys_guid()) GUID
 from dual connect by level <= 2;

显然，我不能使用substr和串联，因为每个SUBSTR将处理不同的SYS_GUID.我也想留在SQL中，而无需上下文切换到PL/SQL函数.

Obviously I can't use substr and concatenation as each SUBSTR would process a different SYS_GUID. I would also like to stay in SQL, without a context switch to PL/SQL function.

任何想法如何使用掩码使用类似于日期或数字的SQL格式格式化字符串:

Any idea how to format string in SQL similar to date or number using a mask:

 to_char(rawtohex(sys_guid(),'CCCCCCCC-CCCC-CCCC-CCCC-CCCCCCCCCCCC') /* note, this is clear illegal */

推荐答案

不幸的是，您不能以数字格式包含字符串文字，否则您可以将十六进制字符串转换为数字，然后再次返回，在格式掩码中插入文字在正确的位置-但是您只能在日期上这样做.

You can't include string literals in number formats unfortunately, otherwise you could convert the hex string to a number and then back again, inserting literals in the format mask in the right places - but you can only do that for dates.

由于位置固定，因此可以使用substr().您担心的是

You can use substr() since the positions are fixed. You were concerned that

显然，我不能使用substr和串联，因为每个SUBSTR都会处理不同的SYS_GUID.

Obviously I can't use substr and concatenation as each SUBSTR would process a different SYS_GUID.

使用子查询分解(也称为公用表表达式/CTE)意味着substr()对该CTE中的一行的调用都具有相同的GUID；此方法不会为每个方法生成一个新的SYS_GUID.

Using subquery factoring (a.ka. a common table expression/CTE) means the substr() calls for a row from that CTE all see the same GUID; this method doesn't generate a new SYS_GUID for each one.

with t as (
  select rawtohex(sys_guid()) guid from dual
  connect by level <= 2
)
select guid, substr(guid, 1, 8)
  ||'-'|| substr(guid, 9, 4)
  ||'-'|| substr(guid, 13, 4)
  ||'-'|| substr(guid, 17, 4)
  ||'-'|| substr(guid, 21, 12) as formatted_guid
from t;

GUID                             FORMATTED_GUID                         
-------------------------------- ----------------------------------------
2F6BA62518F926D0E0534D49E50ABB46 2F6BA625-18F9-26D0-E053-4D49E50ABB46    
2F6BA62518FA26D0E0534D49E50ABB46 2F6BA625-18FA-26D0-E053-4D49E50ABB46    

在大量数据上，这比正则表达式快得多.循环中有100000个值(在PL/SQL块中，在循环内进行最少的工作以使其实际正确评估，并使用dbms_utility.get_cpu_time检查经过的时间)，正则表达式版本大约需要2.51秒，而子字符串版本大约需要0.29秒.您的系统当然会获得不同的数量，但是它仍然应该是大约相同的数量级.

That's a lot faster than the regex on a larger amount of data. With 100000 values in a loop (in a PL/SQL block, doing a minimal amount of work inside the loop to make it actually evaluate properly, and using dbms_utility.get_cpu_time to check the elapsed time) the regex version takes about 2.51 seconds, while the substring version takes about 0.29 seconds. Your system will get different numbers of course, but it should still be about the same order of magnitude.

这篇关于不使用REGEXP_REPLACE和PL/SQL格式化UUID字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！