[SOLVED] Is it possible to convert a SQL FileTable path_locator back to the GUID that was used to generate it

Is it possible to convert a SQL FileTable path_locator back to the GUID that was used to generate it

I'm creating a path in a SQL FileTable using the following code

SET @path = '/' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 1, 6))) + '.' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 7, 6))) + '.' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 13, 4)));

where @customerId is a UniqueIdentifier

I have a a requirement later on when retrieving the document to use the path_locator and get the customerId back from the path.

I've tried all sorts of different ways but I've not been able to find a solution. Below is my TSQL where I start with a customerId and convert it to a path and then try and convert back again. I can get as far as a string representation of a BINARY(16) version of the identifier but then I get stuck.

Any help most appreciated.

DECLARE @customerId     UNIQUEIDENTIFIER;
SET @customerId = '680EC8FA-F54C-44D6-9308-61F421E11E64';
SELECT @customerId AS customerId;

DECLARE @path   VARCHAR(512);
SET @path = '/' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 1, 6))) + '.' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 7, 6))) + '.' +
        CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 13, 4)));

DECLARE @temp   VARCHAR(512);
DECLARE @part1  VARCHAR(20);
DECLARE @part2  VARCHAR(20);
DECLARE @part3  VARCHAR(20);

SET     @part1 = SUBSTRING(@path, 2, CHARINDEX('.', @path) -2);
SET     @temp  = SUBSTRING(@path, CHARINDEX('.', @path) + 1, 500);
SET     @part2 = SUBSTRING(@temp, 1, CHARINDEX('.', @temp) -1);
SET     @part3  = SUBSTRING(@temp, CHARINDEX('.', @temp) + 1, 500);

DECLARE @int1   BIGINT;
DECLARE @int2   BIGINT;
DECLARE @int3   BIGINT;

SET     @int1 = CONVERT(BIGINT, @part1);
SET     @int2 = CONVERT(BIGINT, @part2);
SET     @int3 = CONVERT(BIGINT, @part3);

SELECT  'Target',
        CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 1, 6)),
        CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 7, 6)),
        CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), @customerId), 13, 4));
SELECT  'Result', @part1, @part2, @part3

SELECT  'Target', 
        SUBSTRING(CONVERT(BINARY(16), @customerId), 1, 6),
        SUBSTRING(CONVERT(BINARY(16), @customerId), 7, 6),
        SUBSTRING(CONVERT(BINARY(16), @customerId), 13, 4)

SELECT  'Result',
        CONVERT(BINARY(16), @int1), CONVERT(BINARY(16), @int2), CONVERT(BINARY(16), @int3);

SELECT  CONVERT(BINARY(16), @int1 + @int2 + @int3),master.dbo.fn_varbintohexstr(CONVERT(BINARY(16), @int1 + @int2 + @int3))
        , @customerId, @path, CONVERT(BINARY(16), @customerId)

DECLARE @binaryString VARCHAR(50)
SET @binaryString = RIGHT(master.dbo.fn_varbintohexstr(@int1), 12) +
                    RIGHT(master.dbo.fn_varbintohexstr(@int2), 12) +
                    RIGHT(master.dbo.fn_varbintohexstr(@int3), 8);
SELECT  'Binary String', @binaryString;

Solution

Once you've got the @int variables, you were practically done:

SELECT CONVERT(uniqueidentifier,
            CONVERT(binary(6),@int1) +
            CONVERT(binary(6),@int2) +
            CONVERT(binary(4),@int3))

Which was just the size of the chunks that the original data was sliced into using SUBSTRING.