Re: Reformatting GUIDs

On Dec 20, 11:25 am, bartho..._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

Write a User Defined Function, UDF, that gets the GUID once and then parses the SUID into the output field with the desired additions or subtractions and passes the resulting parameter back.

HTH -- Mark D Powell -- Received on Wed Dec 20 2006 - 10:36:16 CST