MSSQL GUID/UUID from string-CodePudding

I need to create permanent GUIDs/UUIDs from given strings with MSSQL, but found no solution for doing this. So that string "A12345" becomes something like "AAAAAAAA-1234-5678-90AB-AAAAAAAAAAA" every time a function is called. Need some advices or examples how to achieve that.

CodePudding user response：

As someone stated in the comments, you should probably reconsider, BUT.. If string is of type varchar(16) or smaller (not nvarchar), there is a way to atleast approximately achieve what I understand your objective to be.

I do not know enough about your case to recommend this solution, but assuming you actually need to do it.. If nothing else, this might help you build your own function or decide it's a bad idea all together. The solution below requires you to build another function to basically do the same in reverse when retrieving data, if you want the original value back.

A uniqueidentifier is visually represented by a string of 32 alpha-numeric characters between 0-9 and A-F, as well as four dashes. This means that you have 16 options per position in that string, with 32 positions. I'd recommend against using the letter A as a filler in this function, below I've used 0 instead, as 00 is NULL in ASCII and AA would represent an actual character.

Starting off, you can transform each character in the string to its ASCII value with the ASCII() function. Thereafter, you can transform the returned integer to a hexadecimal string representation, by using division and modulus 16 on that value. ASCII ranges from 0-255 in SQL Server, which means there are 256 different characters. 256 is 16 times 16, which means you need two positions in the uniqueidentifier to represent each character in the input string, hence the limit of maximum 16 character strings in this function instead of 32 (see previous paragraphs). Finally, you need to insert the dashes at the correct spots and convert the string to an uniqueidentifier.

 CREATE FUNCTION dbo.MYGUID (@input varchar(17))
    RETURNS uniqueidentifier
    BEGIN
        /* Unable to convert any string langer than 16 characters with this method */
        IF LEN(@input) > 16
            RETURN CAST('FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF' as uniqueidentifier)
    
    DECLARE 
        @result varchar(36) = '',
        @char char(1) = '',
        @placeholder varchar(32) = REPLICATE('0',32)

    /* Convert all characters in string to psuedo-hexadecimal */
    WHILE LEN(@input) > 0 BEGIN
        /* Use first character in input string.. */
        SET @char = LEFT(@input, 1)
        /* Convert character to hexadecimal representation */
        SET @result  = CONVERT(char(1),
            CASE ASCII(@char) / 16
                WHEN 10 THEN 'A'
                WHEN 11 THEN 'B'
                WHEN 12 THEN 'C'
                WHEN 13 THEN 'D'
                WHEN 14 THEN 'E'
                WHEN 15 THEN 'F'
                ELSE CONVERT(char, ASCII(@char) / 16)
            END)
         CONVERT(char(1), 
            CASE ASCII(@char) % 16
                WHEN 10 THEN 'A'
                WHEN 11 THEN 'B'
                WHEN 12 THEN 'C'
                WHEN 13 THEN 'D'
                WHEN 14 THEN 'E'
                WHEN 15 THEN 'F'
                ELSE CONVERT(char, ASCII(@char) % 16)
            END
        )
        /* Remove first character from input string.. */
        SET @input = STUFF(@input, 1, 1, '')
    END

    /* Make sure there are exactly 32 alpha-numeric characters in outgoing string */
    SET @result = RIGHT(@placeholder @result,32)

    /* Insert dashes at the correct positions */
    SET @result = STUFF(@result, 21, 0, '-')
    SET @result = STUFF(@result, 17, 0, '-')
    SET @result = STUFF(@result, 13, 0, '-')
    SET @result = STUFF(@result, 9, 0, '-')

    /* Returns string as uniqueidentifier */
    RETURN CAST(@result as uniqueidentifier)
END

After you've created the function, you can use it by..

SELECT dbo.MYGUID(column1)
FROM table1

..or..

SELECT dbo.MYGUID('A12345')

CodePudding user response：

If you're just looking to create a mapping you can do something like this (example fiddle):

Create a table to hold the mapping

create table StringGuidMap (
    Guid uniqueidentifier not null constraint PK_StringGuidMap primary key clustered default NewSequentialId()
    , StringId nvarchar(8) not null constraint UK_StringGuidMap_StringId unique
)

Create logic to fetch the mapping or create new mappings as required:

create procedure sp_GetIdMapping (
  @StringId nvarchar(8)
) as
begin

  declare @result uniqueidentifier = null

  select @result = Guid 
  from StringGuidMap 
  where StringId = @StringId -- note: case/accent sensitivity depends on your DB's collation

  if (@result is null)
  begin
      
      insert into StringGuidMap(StringId) 
      values (@StringId)
      
      select @result = Guid 
      from StringGuidMap 
      where StringId = @StringId
      
  end

  select @result Guid

end

In the above I've used NewSequentialId for the GUID generation, ensuring that this column can easily be used as a primary key / in an index without causing fragmentation, as each new ID generated would be put on the end of the existing index. However, you can use newid() to generate GUIDs if you prefer / could generate the GUID in the stored procedure instead of as the default value in the insert, thus reducing calls there. Which you go with depends on your requirements

Note: The above is for a mapping; but this mapping will only make sense to someone with the mapping table / it's not something that's universally true; so again, whether this is suitable depends on your requirements.