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.
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')