5 Replies Latest reply on Apr 23, 2010 5:53 AM by dotAge

    How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx

    dotAge
      likes .Net Guid.ToString("D") ?
        • 1. Re: How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
          Herald ten Dam
          Hi,

          you can use UTL_RAW.CAST_TO_VARCHAR2('STRING') to get from RAW to VARCHAR2. This can give some garbage output because of charactersets
          Another option is to use RAWTOHEX('STRING').
          Another is to use: CAST('STRING' as varchar2(32)) , adjust the size to the string.

          Herald ten Dam
          Superconsult.nl

          Edited by: Herald ten Dam on 21-apr-2010 10:42
          • 2. Re: How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
            MichaelS
            Maybe
            SQL> select lower(regexp_replace(sys_guid(),'(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5')) guid from dual
            /
            GUID                                                                            
            --------------------------------------------------------------------------------
            84bab357-6810-a179-e040-007f01001d77                                            
            1 row selected.
            1 person found this helpful
            • 3. Re: How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
              dotAge
              Hi Michaels2,

              Thank for your excellent idea! I think that we are very close to a solution but some endian problems.

              For example:

              select lower(regexp_replace(hextoraw('5907A379B23FAE8BE0400E0A720E23E1'),'(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5')) from dual
              output:
              5907a379-b23f-ae8b-e040-0e0a720e23e1

              but .Net Guid.ToString("D") output
              79a30759-3fb2-8bae-e040-0e0a720e23e1

              The first 3 parts are not match.

              Because Oracle regular expression maximal supports 9 groups, we can't use the the following operators for the function:

              select lower(regexp_replace(hextoraw('5907A379B23FAE8BE0400E0A720E23E1'),'(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})(.{4})(.{12})', '\4\3\2\1-\6\5-\8\7-\9-\10')) from dual
              output:
              79a30759-3fb2-8bae-e040-590

              Any further idea?

              Best Regards,
              dotAge
              • 4. Re: How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
                Herald ten Dam
                Hi,

                do it with a double regexp_replace:
                select lower(regexp_replace(regexp_replace(hextoraw('5907A379B23FAE8BE0400E0A720E23E1'),'(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5').'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18}','\4\3\2\1-\6\5-\8\7\9')) from dual
                The difference about Oracle and Microsoft is a known issue: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/3f913680-2b31-4328-a9bc-99da142d4760 and also in this Oracle blog: http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

                Herald ten Dam
                Superconsult.nl
                • 5. Re: How to convert RAW(16) to varchar format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx
                  dotAge
                  Then the expression is too complex, I'd rather simply use the substr function:

                  declare
                  guidraw raw(16);
                  guidstring varchar(32);
                  tostring varchar2(36);
                  begin
                  guidraw := sys_guid();
                  guidstring := lower(rawtohex(guidraw));
                  select
                  substr(guidstring,7,2) ||
                  substr(guidstring,5,2) ||
                  substr(guidstring,3,2) ||
                  substr(guidstring,1,2) || '-' ||
                  substr(guidstring,11,2) ||
                  substr(guidstring,9,2) || '-' ||
                  substr(guidstring,15,2) ||
                  substr(guidstring,13,2) || '-' ||
                  substr(guidstring,17,4) || '-' ||
                  substr(guidstring,21,12)
                  into tostring from dual;
                  dbms_output.put_line(guidstring);
                  dbms_output.put_line(tostring);
                  end;

                  I did a 100000 loops test, the test result indicated that the performance of the substr solution is much better than the regular expression solution.

                  Thank you anyway!