Search code examples
sqlsql-servermongodbconverters

convert mongodb bindata uuid to sql server uniqueidentifier

How to convert MongoDB bindata uuid to sql server uniqueidentifier?

I have { _id: BinData(3,"FOUmkwBNAEyvGcFpJRpJfA==")} in MongoDB(db.version(): 4.4.11)

I can convert it to UUID by the following command:

db.getCollection('mycollection').findOne({ _id: BinData(3,"FOUmkwBNAEyvGcFpJRpJfA==")})._id.hex()

14e52693004d004caf19c169251a497c

The MS SQL service (Microsoft SQL Server 2017 on Windows Server 2012 R2) uniqueidentifier is

9326E514-4D00-4C00-AF19-C169251A497C

Question How to verify the MongoDB bindata uuid { _id: BinData(3,"FOUmkwBNAEyvGcFpJRpJfA==")} to sql server uniqueidentifier: 9326E514-4D00-4C00-AF19-C169251A497C ?

Solution

  • A SQL Server Uniqueidentifier doesn't have to follow any of the UUID versions, so any 16 bytes can be converted into a uniqueidentifier. And you can cast to uniqueidentifier either from char or from binary types, eg:

    select cast(0x14e52693004d004caf19c169251a497c as uniqueidentifier)

    outputs

    9326E514-4D00-4C00-AF19-C169251A497C