Re: Reformatting GUIDs

Thanks. I am just now learning how to write functions, so this will be good practice.

Bart

Mark D Powell wrote:
> 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:53:55 CST