Search code examples
sqlsql-servercastinguniqueidentifiervarchar

From varchar(36) to UNIQUEIDENTIFIER

I am trying to cast an AuctionId that is a UNIQUEIDENTIFIER to an varchar(36) and then back to an UNIQUEIDENTIFIER. Please help me.

CAST((SUBSTRING(CAST([AuctionId] as VARCHAR(36)), 0, 35) + '1') AS UNIQUEIDENTIFIER)

But I keep getting this error:

Msg 8169, Level 16, State 2, Line 647 Conversion failed when converting from a character string to uniqueidentifier.

Thanks in advance

Solution

  • The '1' is not the problem. You are obviously trying to change the last character of the GUID to a 1. I don't know why, but that's your requirement.

    Your issue is with substring. In TSQL the substring uses an index starting at 1 not 0 like in C or C#. This means your substring statement is actually returning a 34 character string (+1 more character makes 35, and you're being told a 35 character string is not a GUID, which is right).

    Just change the ,0,35 to 1,35