oracle - Formating UUID String without REGEXP_REPLACE and PL/SQL

Question

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') /* note, this is clear illegal */

Answer 1

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.