如何检查字符串是否是唯一标识符? [英] How to check if a string is a uniqueidentifier?

问题描述

uniqueidentifier (SQL Server) 是否有与 IsDate 或 IsNumeric 等效的值?或者有什么等同于 (C#) TryParse 的吗?

Is there an equivalent to IsDate or IsNumeric for uniqueidentifier (SQL Server)? Or is there anything equivalent to (C#) TryParse?

否则我将不得不编写自己的函数，但我想确保我不会重新发明轮子.

Otherwise I'll have to write my own function, but I want to make sure I'm not reinventing the wheel.

我试图涵盖的场景如下:

The scenario I'm trying to cover is the following:

SELECT something FROM table WHERE IsUniqueidentifier(column) = 1

推荐答案

SQL Server 2012 使用 TRY_CONVERT(UNIQUEIDENTIFIER, 表达式)

SQL Server 2012 makes this all much easier with TRY_CONVERT(UNIQUEIDENTIFIER, expression)

SELECT something
FROM   your_table
WHERE  TRY_CONVERT(UNIQUEIDENTIFIER, your_column) IS NOT NULL;

对于以前版本的 SQL Server，现有的答案遗漏了几点，这意味着它们可能与 SQL Server 实际上会强制转换为 UNIQUEIDENTIFIER 的字符串不匹配，或者仍然可能导致无效转换错误.

For prior versions of SQL Server, the existing answers miss a few points that mean they may either not match strings that SQL Server will in fact cast to UNIQUEIDENTIFIER without complaint or may still end up causing invalid cast errors.

SQL Server 接受包含在 {} 中或不包含此内容的 GUID.

SQL Server accepts GUIDs either wrapped in {} or without this.

此外，它会忽略字符串末尾的无关字符.SELECT CAST('{5D944516-98E6-44C5-849F-9C277833C01B}ssssssssss' as uniqueidentifier) 和 SELECT CAST('5D944516-98E6-44C5-XXXXXXX3CAST XX8XXXXXXX3C01B}sssssssss' as uniqueidentifier) 和 SELECT CAST('5D944516-98E6-44C5-849F-9C277833C01B}ssssssssss' as uniqueidentifier)/code> 例如成功.

Additionally it ignores extraneous characters at the end of the string. Both SELECT CAST('{5D944516-98E6-44C5-849F-9C277833C01B}ssssssssss' as uniqueidentifier) and SELECT CAST('5D944516-98E6-44C5-849F-9C277833C01BXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' as uniqueidentifier) succeed for instance.

在大多数默认排序规则下，LIKE '[a-zA-Z0-9]' 将结束匹配的字符，例如 À 或 Ë

Under most default collations the LIKE '[a-zA-Z0-9]' will end up matching characters such as À or Ë

最后，如果将结果中的行转换为 uniqueidentifier，将转换尝试放在 case 表达式中很重要，因为转换可能发生在 WHERE 过滤行之前.

Finally if casting rows in a result to uniqueidentifier it is important to put the cast attempt in a case expression as the cast may occur before the rows are filtered by the WHERE.

所以(借用@r0d30b0y 的想法)一个稍微更强大的版本可能是

So (borrowing @r0d30b0y's idea) a slightly more robust version might be

;WITH T(C)
     AS (SELECT '5D944516-98E6-44C5-849F-9C277833C01B'
         UNION ALL
         SELECT '{5D944516-98E6-44C5-849F-9C277833C01B}'
         UNION ALL
         SELECT '5D944516-98E6-44C5-849F-9C277833C01BXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
         UNION ALL
         SELECT '{5D944516-98E6-44C5-849F-9C277833C01B}ssssssssss'
         UNION ALL
         SELECT 'ÀD944516-98E6-44C5-849F-9C277833C01B'
         UNION ALL
         SELECT 'fish')
SELECT CASE
         WHEN C LIKE expression + '%'
               OR C LIKE '{' + expression + '}%' THEN CAST(C AS UNIQUEIDENTIFIER)
       END
FROM   T
       CROSS APPLY (SELECT REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]') COLLATE Latin1_General_BIN) C2(expression)
WHERE  C LIKE expression + '%'
        OR C LIKE '{' + expression + '}%' 

这篇关于如何检查字符串是否是唯一标识符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！