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

问题描述

我想格式化sys_guid（）函数的结果，如这个答案

  select regexp_replace（rawtohex（sys_guid（））
 （[A-F0-9] {4}）（[A-F0-9] {4}）（[A-F0-9] {4}） （[A-F0-9] {12}）'
，'\1- \2- \ 3-\ 4-\ 5'）
为FORMATTED_GUID 
从双
  

由于性能原因，我想避免使用regexp_replace（当我处理大量的记录）。

我的场景可以简化为这个用例：

  select rawtohex（sys_guid（））GUID 
通过级别双连接< = 2; 
  

显然我不能使用substr和concatenation，因为每个SUBSTR都会处理不同的SYS_GUID。我也想留在SQL中，没有上下文切换到PL / SQL函数。

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

  to_char（rawtohex（sys_guid（），'CCCCCCCC-CCCC-CCCC-CCCC-CCCCCCCCCCCC'）/ * not this is清除非法* / 
  

解决方案

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

您可以使用 substr（），因为头寸是固定的，您担心的是

显然，我不能使用substr和concatenation，因为每个SUBSTR将处理不同的SYS_GUID。

使用子查询分解（a.ka.公用表表达式/ CTE）意味着 substr（）调用fo从CTE开始的一行都看到相同的GUID; （
）select rawtohex（sys_guid（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秒。你的系统当然会得到不同的数字，但它应该仍然是相同的数量级。

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

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;

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.

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') /* not 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.

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

Obviously I can't use substr and concatenation as each SUBSTR would process a different 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    

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来格式化字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！