Store guid received from sqlserver as raw in oracle

user5716448 wrote:

 

Thanks again for reply.

 

Is there an equivalent once we have this data in the raw datatype column to view it's equivalent as varchar2 as can then feed back any queries to sqlserver team with guid as they know it?

Sure :

SQL> with input (guid_raw) as (
  2    select hextoraw('7A3AC7EA9393F8469BC5271E2C034110') from dual
  3  )
  4  select lower(
  5           regexp_replace(substr(guid_raw,1,20), '^(..)(..)(..)(..)(..)(..)(..)(..)(.{4})$', '\4\3\2\1-\6\5-\8\7-\9-') ||
  6           substr(guid_raw,21)
  7         ) as guid_string
  8  from input;

GUID_STRING
------------------------------------
eac73a7a-9393-46f8-9bc5-271e2c034110

user5716448 wrote:

 

get you are converting to raw and removing all the - but would be good if could assist my understanding what is happening with the bit in bold

 

select hextoraw(

regexp_replace(replace('eac73a7a-9393-46f8-9bc5-271e2c034110','-')

, '^(..)(..)(..)(..)(..)(..)(..)(..)(.{16})$'

, '\4\3\2\1\6\5\8\7\9'

)

) as guid_raw

from dual;

It's a regular expression pattern.

^   - matches the beginning of the string

()  - declares a capturing group

.   - matches any character

{n} - fixed quantifer that applies to the preceding subpattern

$   - matches the end of the string

So in the above pattern, we are trying to match a sequence of 8 groups of 2 characters, followed by a group of 16 characters.

In the 3rd parameter of REGEXP_REPLACE, we can reference groups found in the input string by their occurring order : \1 will then be replaced by the first group, and so on, but we can only reference up to 9 groups like this.

Since you required a variant-2 format, the first 3 parts of the GUID must be stored little-endian, that's why we're reversing the order of the byte sequence, and the remaining 8 bytes big-endian, so we can leave them as is.

\1 = ea

\2 = c7

\3 = 3a

\4 = 7a

\5 = 93

\6 = 93

\7 = 46

\8 = f8

\9 = 9bc5271e2c034110

therefore \4\3\2\1\6\5\8\7\9 = 7a3ac7ea9393f8469bc5271e2c034110

Pretty much the same reasoning for the other expression.