Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 19, 2020 - 9:28 am UTC

Answered by: Chris Saxon - Last updated: October 20, 2020 - 1:21 pm UTC

Category: SQL - Version: 12.1.0.1

Viewed 100+ times

You Asked

Hi Chirs, Connor,
Could you please help or suggest a way to generate string based on pattern

Pattern - ^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$

I need to generate millions of string like f9f8c8e2-0b20-4160-8f74-e836f4661fc5 matches with the pattern.

e.g.
SELECT * FROM dual WHERE REGEXP_LIKE('f9f8c8e2-0b20-4160-8f74-e836f4661fc5', '^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');



and we said...

What exactly are you generating these strings for? That looks suspiciously like an MD5 hash.

If that's what you're looking for, you can generate on for an input value using standard hash:

select lower ( standard_hash ( dummy, 'MD5' ) ) str
from   dual;

STR                                
02129bb861061d1a052c592e2dc6b383 


If you need it formatted as shown, use some substr manipulation:

with rws as (
  select lower ( standard_hash ( dummy, 'MD5' ) ) str
  from   dual
)
  select substr ( str, 1, 8 ) || '-' || 
         substr ( str, 9, 4 ) || '-' || 
         substr ( str, 13, 4 ) || '-' || 
         substr ( str, 17, 4 ) || '-' || 
         substr ( str, 21 ) md5
  from   rws;
  
MD5                                    
02129bb8-6106-1d1a-052c-592e2dc6b383   


This gives you a different value for each input string:

with rws as (
  select level val, lower ( standard_hash ( level, 'MD5' ) ) str
  from   dual
  connect by level <= 5
)
  select substr ( str, 1, 8 ) || '-' || 
         substr ( str, 9, 4 ) || '-' || 
         substr ( str, 13, 4 ) || '-' || 
         substr ( str, 17, 4 ) || '-' || 
         substr ( str, 21 ) md5
  from   rws;
  
MD5                                    
dcf82b57-bab4-a7fc-b9c2-f14353c7460b    
8dafe7ba-c683-b104-0a19-fc449c8f8743    
42f95b73-6dba-041c-a8f8-f3ae0af67660    
155da696-6e67-ad17-7958-54cf5e2ee33e    
fc966bc5-914a-0499-b45e-554aedf3418f


If you need random strings with hex values 1 - F, you can use dbms_random to generate values 1 - 16. Then to_char them to hex with the XX format mask:
select listagg ( to_char ( dbms_random.value ( 1, 16 ), 'fmxx' ) )
         within group ( order by level ) str
from   dual
connect by level <= 8

STR         
67fb3eb10  

and you rated our response

  (4 ratings)

Reviews

Try sys_guid()

October 20, 2020 - 11:09 am UTC

Reviewer: Mikhail Onishchenko from Moscow Russia

It looks like you're in need of a GUID. Try using sys_guid():

select sys_guid()
from dual;


It has the same number of meaningful characters as the example you have provided. Format it as Chris suggested earlier:

with rws as (
  select lower ( sys_guid() ) str
  from   dual
)
  select substr ( str, 1, 8 ) || '-' || 
         substr ( str, 9, 4 ) || '-' || 
         substr ( str, 13, 4 ) || '-' || 
         substr ( str, 17, 4 ) || '-' || 
         substr ( str, 21 ) md5
  from   rws;

Chris Saxon

Followup  

October 20, 2020 - 1:21 pm UTC

Ah yes, that works too :)

is SYS_GUID a v4 UUID?

October 20, 2020 - 7:56 pm UTC

Reviewer: paul from Ottawa, Canada

His format was explicit [89ab][a-f][0-9]{3} indicates he is being explicit about the format of the 2nd last octet.

SYS_GUID as 16 byte raw does not ( I think) conform to UUID v4 format

with rws as (select sys_guid() str from dual)
select substr ( str, 1, 8 ) || '-' || 
         substr ( str, 9, 4 ) || '-' || 
         substr ( str, 13, 4 ) || '-' || 
         substr ( str, 17, 4 ) || '-' || 
         substr ( str, 21 ) uuid
  from   rws;
  

uuid
-----
B2204BE7-1155-1D1A-E053-02A0C40A4E5B

To say nothing of the Endian ordering...
that wont match his regex



Just generate the string by chunks then

October 22, 2020 - 8:12 am UTC

Reviewer: Mikhail Onishchenko from Moscow Russia

You're right, I didn't notice the [89ab] part of the pattern.

Well then, he can just generate random numbers and format them into a desirable shape.

select lower(
        trim(to_char(floor(dbms_random.value(0, power(16, 4))), 'XXXX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 8))), 'XXXXXXXX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 4))), 'XXXX')) || '-' ||
        case floor(dbms_random.value(0, 4))
          when 0 then '8'
          when 1 then '9'
          when 2 then 'A'
          when 3 then 'B'
        end ||
        trim(to_char(floor(dbms_random.value(0, power(16, 3))), 'XXX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 8))), 'XXXXXXXX')) ||
        trim(to_char(floor(dbms_random.value(0, power(16, 4))), 'XXXX'))
       )
from dual;


You can put in the explicit values of powers of 16, though I'm not sure how much resources that would conserve if any.

Follow up on format

October 22, 2020 - 8:22 am UTC

Reviewer: Mikhail Onishchenko from Moscow Russia

Oh, and use formats like '0XXX', or the leading zeroes will be lost:

select lower(
        trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 8))), '0XXXXXXX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 4))), '0XXX')) || '-' ||
        case floor(dbms_random.value(0, 4))
          when 0 then '8'
          when 1 then '9'
          when 2 then 'A'
          when 3 then 'B'
        end ||
        trim(to_char(floor(dbms_random.value(0, power(16, 3))), '0XX')) || '-' ||
        trim(to_char(floor(dbms_random.value(0, power(16, 12))), '0XXXXXXXXXXX'))
       )
from dual;

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.