英文:
SQL - uniqueidentifier in where clause
问题
我正在使用SSMS v18。
SQL Server版本：Microsoft SQL Azure RTM 12.0.200.8
我有3500个唯一标识符需要在where子句中进行比较或保留。
select *
from tableName
where columnName in ('6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
.
.
.
)
columnName 在这里是唯一标识符。
就像这样，我有3500个唯一标识符要在where子句中比较。
我遇到了这个错误：
> 转换失败，无法将字符转换为唯一标识符
当我在查询中使用 convert(nvarchar(36, columnName)) 时，查询会运行5分钟，但不返回任何结果。
英文:
I am using SSMS v18.
SQL Server version: Microsoft SQL Azure RTM 12.0.200.8
I have 3500 unique identifiers to compare or to keep in where clause.
select *
from tableName
where columnName in ('6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
.
.
.
)
columnName is unique identifier here.
Like this I have 3500 unique identifiers to compare in where clause.
I am getting this error:
> Conversion failed when converting from a character to uniqueidentifier
when I use convert(nvarchar(36, columnName)) in query - the query keeps running for 5 minutes but doesn't return any results.
答案1
得分: 1
请尝试以下解决方案。
总体而言，缺少将数据转换为适当数据类型。
VARCHAR(...) 数据类型与 UNIQUEIDENTIFIER 数据类型不匹配。
SQL
-- DDL 和示例数据填充，开始
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col UNIQUEIDENTIFIER);
INSERT INTO @tbl (col) VALUES
(TRY_CAST('6B29FC40-CA47-1067-B31D-00DD010662DA' AS UNIQUEIDENTIFIER)),
(TRY_CAST('C49CBB38-2C1E-43DE-8A9D-3DECB6B4F183' AS UNIQUEIDENTIFIER));
-- DDL 和示例数据填充，结束
-- 方法 #1
SELECT * FROM @tbl
WHERE TRY_CAST(col AS VARCHAR(40)) IN
(
'6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
);
-- 方法 #2
SELECT t.*
FROM @tbl AS t INNER JOIN
(VALUES
('6B29FC40-CA47-1067-B31D-00DD010662DA'),
('7C59FC31-SA87-1667-B31D-00DD010772DA')
) AS UniqueIDs(col)
ON t.col = TRY_CAST(UniqueIDs.col AS UNIQUEIDENTIFIER);
英文:
Please try the following solution.
Overall, casting to a proper data type was missing.
VARCHAR(...) data type is not a UNIQUEIDENTIFIER data type.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col UNIQUEIDENTIFIER);
INSERT INTO @tbl (col) VALUES
(TRY_CAST('6B29FC40-CA47-1067-B31D-00DD010662DA' AS UNIQUEIDENTIFIER)),
(TRY_CAST('C49CBB38-2C1E-43DE-8A9D-3DECB6B4F183' AS UNIQUEIDENTIFIER));
-- DDL and sample data population, end
-- Method #1
SELECT * FROM @tbl
WHERE TRY_CAST(col AS VARCHAR(40)) in
(
'6B29FC40-CA47-1067-B31D-00DD010662DA',
'7C59FC31-SA87-1667-B31D-00DD010772DA'
);
-- Method #2
SELECT t.*
FROM @tbl AS t INNER JOIN
(VALUES
('6B29FC40-CA47-1067-B31D-00DD010662DA'),
('7C59FC31-SA87-1667-B31D-00DD010772DA')
) AS UniqueIDs(col)
ON t.col = TRY_CAST(UniqueIDs.col AS UNIQUEIDENTIFIER);
答案2
得分: 1
在那3500个值中，您可以为以下内容添加前缀：
select *
from tableName
where
columnName in
(
SELECT
CONVERT(uniqueidentifier,'6B29FC40-CA47-1067-B31D-00DD010662DA'),
SELECT
CONVERT(uniqueidentifier,'7C59FC31-SA87-1667-B31D-00DD010772DA')
.
.
.
)
英文:
in those 3500 values you can add a prefix for below
select *
from tableName
where
columnName in
(
SELECT
CONVERT(uniqueidentifier,'6B29FC40-CA47-1067-B31D-00DD010662DA'),
SELECT
CONVERT(uniqueidentifier,'7C59FC31-SA87-1667-B31D-00DD010772DA')
.
.
.
)
答案3
得分: -1
我使用以下格式解决了它 -
SELECT *
FROM tableName
WHERE columnName IN (
SELECT TRY_CONVERT(uniqueidentifier, '6B29FC40-CA47-1067-B31D-00DD010662DA') UNION ALL
SELECT TRY_CONVERT(uniqueidentifier, '7C59FC31-SA87-1667-B31D-00DD010772DA')
-- 为其余的唯一标识符添加更多的SELECT语句
)
英文:
I resolved it using format below -
SELECT *
FROM tableName
WHERE columnName IN (
SELECT TRY_CONVERT(uniqueidentifier, '6B29FC40-CA47-1067-B31D-00DD010662DA') UNION ALL
SELECT TRY_CONVERT(uniqueidentifier, '7C59FC31-SA87-1667-B31D-00DD010772DA')
-- Add more SELECT statements for the remaining unique identifiers
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论