CODEWITHSUNDEEP
sql-serveruniqueidentifier
Ed James
Ed James

Reputation: 10607

Compare Varchar and UniqueIdentifier

Due to a rather brilliant oversight in my current project, we have some guids getting stored in a varchar column in one table, which need to be compared to a uniqueidentifier column in another.

How can I do this? SQL server simply says it cannot convert from a character string to a uniqueidentifier.

Upvotes: 12

Views: 37403

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294287

If SQL complains it cannot cast it means not only you stored the uniqueidentifier as varchar, you used a different format than SQL Server (eg. you added the '{' and '}'). SQL is perfectly capable of casting string to uniqueidentifier when properly formatted:

declare @u uniqueidentifier;
declare @s varchar(64);

select @u = NEWID();
select @s = CAST(@u as varchar(64));
select CAST(@s as uniqueidentifier), @u, @s;

Depending on how you actualy stored the uniqueidentifier, you will most likely have tomodify the data and your code to match the SQL format (no {}).

Upvotes: 20

Philip Kelley
Philip Kelley

Reputation: 40319

I just worked up the following test script:

DECLARE
  @Foo Uniqueidentifier
 ,@Foo2 varchar(50)

SET @Foo = newid()
SET @Foo2 = newId()

print @Foo
print @Foo2

if @Foo = @Foo2
    print 'Yes'
else
    print 'No'

set @Foo = @Foo2

if @Foo = @Foo2
    print 'Yes'
else
    print 'No'

Run in an SSMS window or via slqcmd -i file, the results are the same -- SQL (2005) does implicit conversion. This supports what I recall from SQL 2000 when I had a similar problem years ago.

The key thing is that the varchar string has to match the guid pattern:

  • 8 hex digits
  • dash
  • 4 hex digits
  • dash
  • 4 hex digits
  • dash
  • 4 hex digits
  • dash
  • 12 hex digits

Upvotes: 5

gbn
gbn

Reputation: 432271

You'll have to cast the other uniqueidentifier to varchar.

SQL Server is probably tryng to cast things like "bob" to uniqueidentifier and it fails. According to CAST/CONVERT it's allowed, so it must be the values in the varchar column.

Upvotes: 1

K.A.D.
K.A.D.

Reputation: 3718

Convert the uniqueidentifier to varchar:

CAST( uniqueidentifier_col_name as varchar)

Upvotes: 7

Related Questions