| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Reformatting GUIDs
barthome1_at_comcast.net wrote:
> Here is an older message from this group. The point is to reformat an
> Oracle GUID into the SQLServer format.
> *******************************
> Troy,
>
> Built-in HEXTORAW function will handle the char to raw conversion:
>
> HEXTORAW(TRANSLATE(your_guid_string,'0{-}','0'))
>
> TRANSLATE is there to remove those curly braces and dashes from the
> input string before converting it to raw. For converting a raw guid
> back
> to formatted string you will probably need to create your own
> formatting
> function that will insert dashes in proper positions. To get a
> converted
> string without dashes you use RAWTOHEX built-in function on raw guid
> value. You can then insert dashes into it like this:
>
> return
> substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)......
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> ******************************
>
> I need to do the same thing inline within a SQL select statement. The
> best I can figure to translate this is;
> select
> substr(RAWTOHEX(sys_guid()),1,8)||'-'||substr(RAWTOHEX(sys_guid()),9,4)||---ETC.
>
> The problem I see is that each call to sys_guid() returns another GUID,
> so I am merging the results of several GUIDs. I doubt that will keep
> my GUIDs unique (or will it)?
>
> Any suggestions how to get around this?
>
> Bart
I wonder if you can look at this from a different direction. What if you use the random number generating capabilities to generate each of the hex numbers between the dashes?
For example, the second section of the GUID contains a four character
hex number. If you have Oracle provide a hex number between hex 1000
and hex FFFF, that would be sufficient to satisfy that portion of the
GUID. If you pre-convert the hex numbers to their decimal equivalents,
you will save Oracle a little work, but it will be more difficult to
see what is happening. For example:
SELECT
TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('1000','XXXX')+1,TO_NUMBER('FFFF','XXXX')+1)),'XXXX'))
P2
FROM
DUAL;
P2
==
8FC4
1 ROW SELECTED
Extending this approach by sliding the above into an inline view:
SELECT
P1||'-'||P2 GUID,
P1||'-'||P2 GUID_AGAIN
FROM
(SELECT
TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('10000000','XXXXXXXX')+1,TO_NUMBER('FFFFFFFF','XXXXXXXX')+1)),'XXXXXXXX'))
P1,
TRIM(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(TO_NUMBER('1000','XXXX')+1,TO_NUMBER('FFFF','XXXX')+1)),'XXXX'))
P2
FROM
DUAL);
GUID GUID_AGAIN
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Dec 20 2006 - 11:41:48 CST
 |
 |