CODEWITHSUNDEEP
sqlsql-servercastinguniqueidentifiervarchar
user324169
user324169

Reputation: 15

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

Upvotes: 1

Views: 8922

Answers (3)

Ed Harper
Ed Harper

Reputation: 21495

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

Upvotes: 2

Robin Day
Robin Day

Reputation: 102468

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.

Upvotes: 3

David
David

Reputation: 25450

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

Upvotes: 5

Related Questions