How do I join these two together? Varchar guid and guid type both primary keys

sql, sql-server, sql-server-2008, t-sql

Solution

SQL Server is quit intelligent when it comes to comparing values having different datatypes.

Following script works as is without any explicit conversion

DECLARE @TableA TABLE (VARCHARID VARCHAR(48) PRIMARY KEY)
DECLARE @TableB TABLE (GUIDID UNIQUEIDENTIFIER PRIMARY KEY)

INSERT INTO @TableA VALUES 
    ('{0CAF3FBC-3C76-420B-B0C4-42867551E3B5}')
  , ('{0CAF3FBC-3C76-420B-B0C4-42867551E3B6}')
  , ( '0CAF3FBC-3C76-420B-B0C4-42867551E3B7' )
  , ( '0CAF3FBC-3C76-420B-B0C4-42867551E3B8' )

INSERT INTO @TableB VALUES 
    ('{0CAF3FBC-3C76-420B-B0C4-42867551E3B5}')
  , ( '0CAF3FBC-3C76-420B-B0C4-42867551E3B6' )
  , ('{0CAF3FBC-3C76-420B-B0C4-42867551E3B7}')
  , ( '0CAF3FBC-3C76-420B-B0C4-42867551E3B8' )

SELECT  *
FROM    @TableA a
        INNER JOIN @TableB b ON b.GUIDID = a.VARCHARID

Result

VARCHARID                               GUIDID
{0CAF3FBC-3C76-420B-B0C4-42867551E3B5}  0CAF3FBC-3C76-420B-B0C4-42867551E3B5
{0CAF3FBC-3C76-420B-B0C4-42867551E3B6}  0CAF3FBC-3C76-420B-B0C4-42867551E3B6
0CAF3FBC-3C76-420B-B0C4-42867551E3B7    0CAF3FBC-3C76-420B-B0C4-42867551E3B7
0CAF3FBC-3C76-420B-B0C4-42867551E3B8    0CAF3FBC-3C76-420B-B0C4-42867551E3B8

Problem

I'm trying to compare two different column types (not my doing, part of a data conversion project). One has form guids stored similar to the following: ``` 0CAF3FBC-3C76-420B-B0C4-42867551E3B5 ``` The other has them stored as the following: ``` {0CAF3FBC-3C76-420B-B0C4-42867551E3B5} ``` What would be the best way to join both of these tables on this column (unfortunately it's a primary key for both). So far I've tried casting one table as a guid (with no success) and also tried to build a like statement - but have been unsure how to approach this. Any help would be very greatly appreciated. Edit: In case it's the data, is this syntactically correct? ``` inner join report_temp.workflow_lease_proposal lp on wif.form_guid like '%' + lp.form_guid + '%' ```

Original source