Re: Reformatting GUIDs

I got the function working. Here it is if it will help anyone.

Bart

CREATE OR REPLACE
FUNCTION get_guid
RETURN VARCHAR
IS

   guid VARCHAR (50);
BEGIN
   guid := lower(RAWTOHEX(sys_guid()));
   RETURN
substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)||'-'||substr(guid,17,4)||'-'||substr(guid,21,12); END; barthome1_at_comcast.net wrote:
> 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 - 11:34:44 CST