NVARCHAR TO UNIQUEIDENTIFIER Conversion

Question

I need to copy data from one SQL table to another table where I need this conversion, I tried the following:

SELECT TOP 10 CAST(Per_ID as UniqueIdentifier FROM Test

SELECT TOP 10 CONVERT(UniqueIdentifier,Per_ID,100) FROM Test

Both giving me this error:

Conversion failed when converting from a character string to uniqueidentifier.

Per_ID 1915DE0055FA11D6A6E40008C7D0BAB1 – Arvinder Rehal Sep 22 '13 at 16:43 — Arvinder Rehal, Sep 22 '13 at 16:43

skjcyber · Answer 1 · 2013-09-22T17:38:39.087

You can create a user defined function for this conversion (assuming MS SQL Server is being used):

IF OBJECT_ID (N'dbo.NvarcharToUniqueidentifier', N'FN') IS NOT NULL
    DROP FUNCTION dbo.NvarcharToUniqueidentifier;
GO
CREATE FUNCTION dbo.NvarcharToUniqueidentifier (@id nvarchar(100))
RETURNS UNIQUEIDENTIFIER
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN CAST(LEFT(@id, 8)
            + '-' +RIGHT(LEFT(@id, 12), 4)
            + '-' +RIGHT(LEFT(@id, 16), 4)
            + '-' +RIGHT(LEFT(@id, 20), 4)
            + '-' +RIGHT(@id, 12) 
            AS UNIQUEIDENTIFIER);
END
GO

And you can call this function as below:

select dbo.NvarcharToUniqueidentifier('1915DE0055FA11D6A6E40008C7D0BAB1')

The output is: 1915DE00-55FA-11D6-A6E4-0008C7D0BAB1

score 0 · Answer 2 · answered Sep 22 '13 at 16:38

0

Uniqueindentifier can only contain HEX values, it means A-F and 0-9.

answered Sep 22 '13 at 16:38

Senaid

1

Per_ID 1915DE0055FA11D6A6E40008C7D0BAB1 (This ID is having HEX Value) – Arvinder Rehal Sep 22 '13 at 16:40

score 0 · Answer 3 · answered Sep 22 '13 at 18:21

you can use this script to convert varchar to GUID

DECLARE @uuid VARCHAR(50)
SET @uuid = '1915DE0055FA11D6A6E40008C7D0BAB1'
SELECT  CAST(
        SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
        SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
        AS UNIQUEIDENTIFIER)

NVARCHAR TO UNIQUEIDENTIFIER Conversion

3 Answers3