Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Reformatting GUIDs

Re: Reformatting GUIDs

From: <barthome1_at_comcast.net>
Date: 20 Dec 2006 08:53:55 -0800
Message-ID: <1166633634.962539.187650@80g2000cwy.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US