GUID/UUID Function

When transferring data from a Microsoft SQL Server, the DataVerse requires a Unique Identifier field. The ID field is separate from the Primary Name field and is only accessible through data connectors that exist in tools like Power Automate, Power BI or Azure Data Factory. Because of this it may prove necessary to generate a Unique ID, but for existing data you want to "Upsert" you will want to keep the unique identifier aligned with your data set, so this function will return your unique identifer for your data based on a provided primary key input.

Create Function

----------------------------------------------------------------------------------------------------------------------------------------------------
   
-- MYGUID Function
-- Created By: Erik Blomgren
-- Description: Creates a function that generates a GUID/UUID from a primary key input
-- Website: https://stackoverflow.com/questions/72478934/mssql-guid-uuid-from-string
   
----------------------------------------------------------------------------------------------------------------------------------------------------
 
-- Use Database Declaration (Please set before running)
USE AdventureWorks2017;
GO

-- Create the function
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
;

Function use within SELECT statement

SELECT 	dbo.MYGUID([ID]) as [crm111_mytableid],
		[ID] as [crm111_id],
		[DataColumn1] as [crm111_datacolumn1],
		[DataColumn2] as [crm111_datacolumn2],
		[DataColumn3] as [crm111_datacolumn3]
FROM dbo.vDataVerseView

Source

https://stackoverflow.com/questions/72478934/mssql-guid-uuid-from-string

  • No labels