From varchar(36) to UNIQUEIDENTIFIER

Question

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

Answer 1

Your error is due to your +'1' and your SUBSTRING. What do you have that in there for?

This will work fine

SELECT cast((cast(NEWID() as varchar(36))) as UNIQUEIDENTIFIER)

EDIT: Ok, so if you want to replace the last char with a '1' then this is the solution

SELECT CAST(SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 1, 35) + '1' AS UNIQUEIDENTIFIER)

The only difference is that SUBSTRING in SQL starts at position 1, not position 0 as you had it.

P.S. This is dangerous code. The output is no longer a GUID as it will not conform to the algorithm that was used to generate the GUID. This could (although unlikely) result in a collision with GUIDs which could potentially cause all manner of problems.

Answer 2

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

Answer 3

As others have observed, it's not clear why you want to do what you're doing.

An alternative to SUBSTRING is the STUFF command:

SELECT stuff(cast([AuctionId] as varchar(36)),36,1,'1')