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}$');
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