我如何将这两者结合在一起?Varchar guid 和 guid 都输入主键
我正在尝试比较两种不同的列类型(不是我做的,是数据转换项目的一部分).一个存储的表单 guid 类似于以下内容:
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}
在此列上连接这两个表的最佳方法是什么(不幸的是,它是两者的主键).到目前为止,我已经尝试将一张表作为 guid(没有成功)并尝试构建一个 like 语句 - 但一直不确定如何处理这个.
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.
非常感谢任何帮助.
如果是数据,这在语法上是否正确?
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 + '%'
推荐答案
SQL Server 在比较具有不同数据类型的值时非常智能.
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
结果
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
相关文章