Convert Base 64 value to a GUID
That code looks like this:
FUNCTION Base64ToGuidString(pBase64 in Varchar2) return Varchar2 as vRaw VARCHAR2(32); vGuid VARCHAR2(36); BEGIN vGuid := ''; if (pBase64 is not null and (length(pBase64) > 23)) then vRaw := UTL_ENCODE.base64_decode(utl_raw.cast_to_raw(pBase64));
vGuid := substr(vRaw, 7, 2) || substr(vRaw, 5, 2) || substr(vRaw, 3, 2) || substr(vRaw, 1, 2) || '-' || substr(vRaw, 11, 2) || substr(vRaw, 9, 2) || '-' || substr(vRaw, 15, 2) || substr(vRaw, 13, 2) || '-' || substr(vRaw, 17, 4) || '-' || substr(vRaw, 21, length(vRaw) - 20);
end if;
return lower(vGuid); END;
Well, that's nice enough if you have a UTL_ENCODE package to lean on. In order to do this on SQL Server 2012, I decided to leverage the excellent XML extensions to T-SQL. Using the XML datatype, one gains access to XML Schemas, wherein we find the ability to convert a base 64 string to a binary. From there, its a simple CONVERT to uniqueidentifier, and then CONVERT to nvarchar.
The SQL Server version looks like this:
CREATE FUNCTION Base64ToGuidString (@pBase64 varchar(max)) RETURNS varchar(max) AS BEGIN
DECLARE @vGuid varchar(36); SELECT @vGuid = convert(nvarchar(36), convert(uniqueidentifier, CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@pBase64"))' ,'VARBINARY(MAX)') ));
RETURN lower(@vGuid);
END
Man, I love this stuff!