Compare Varchar and UniqueIdentifier

Question

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.

Answer 1

Convert the uniqueidentifier to varchar:

CAST( uniqueidentifier_col_name as varchar)

Answer 2

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.

Answer 3

If SQL complains it cannot cast it measn 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 {}).

Answer 4

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