SQL 如何检查字符串是否是唯一标识符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4649317/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if a string is a uniqueidentifier?
提问by Benoittr
Is there an equivalent to IsDate or IsNumeric for uniqueidentifier (SQL Server)? Or is there anything equivalent to (C#) TryParse?
是否有等效于 IsDate 或 IsNumeric 的 uniqueidentifier (SQL Server)？或者有什么等同于（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
回答by Martin Smith
SQL Server 2012 makes this all much easier with TRY_CONVERT(UNIQUEIDENTIFIER, expression)
SQL Server 2012 使这一切变得更容易 TRY_CONVERT(UNIQUEIDENTIFIER, expression)
SELECT something
FROM your_table
WHERE TRY_CONVERT(UNIQUEIDENTIFIER, your_column) IS NOT NULL;
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 UNIQUEIDENTIFIERwithout complaint or may still end up causing invalid cast errors.
对于 SQL Server 的早期版本，现有答案遗漏了一些要点，这意味着它们可能与 SQL Server 实际上会强制转换的字符串不匹配，UNIQUEIDENTIFIER或者可能最终仍会导致无效的转换错误。
SQL Server accepts GUIDs either wrapped in {}or without this.
SQL Server 接受包含{}或不包含此内容的GUID 。
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.
此外，它会忽略字符串末尾的无关字符。无论SELECT CAST('{5D944516-98E6-44C5-849F-9C277833C01B}ssssssssss' as uniqueidentifier)和SELECT CAST('5D944516-98E6-44C5-849F-9C277833C01BXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' as uniqueidentifier)例如成功。
Under most default collations the LIKE '[a-zA-Z0-9]'will end up matching characters such as àor ?
在大多数默认排序规则下，LIKE '[a-zA-Z0-9]'最终将匹配字符，例如à或?
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.
最后，如果将结果中的行转换为 uniqueidentifier，将转换尝试放在 case 表达式中很重要，因为转换可能在行被WHERE.
So (borrowing @r0d30b0y's idea) a slightly more robust version might be
所以（借用@r0d30b0y 的想法）一个稍微更强大的版本可能是
;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 + '}%'
回答by r0d30b0y
Not mine, found this online... thought i'd share.
不是我的，在网上找到的……我想分享一下。
SELECT 1 WHERE @StringToCompare LIKE
REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]');
回答by onedaywhen
SELECT something
FROM table1
WHERE column1 LIKE '[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]';
UPDATE:
更新：
...but I much prefer the approach in the answer by @r0d30b0y:
...但我更喜欢@r0d30b0y 的答案中的方法：
SELECT something
FROM table1
WHERE column1 LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]');
回答by marc_s
I am not aware of anything that you could use "out of the box" - you'll have to write this on your own, I'm afraid.
我不知道您可以“开箱即用”使用任何东西-恐怕您必须自己编写。
If you can: try to write this inside a C# library and deploy it into SQL Server as a SQL-CLR assembly - then you could use things like Guid.TryParse()which is certainly much easier to use than anything in T-SQL....
如果可以：尝试在 C# 库中编写它并将其作为 SQL-CLR 程序集部署到 SQL Server 中 - 那么你可以使用类似的东西Guid.TryParse()，它肯定比 T-SQL 中的任何东西都更容易使用......
回答by Mike
Like to keep it simple. A GUID has four - in it even, if is just a string
喜欢保持简单。GUID 有四个 - 甚至，如果只是一个字符串
WHERE column like '%-%-%-%-%'
WHERE 列如 '%-%-%-%-%'
回答by Dave
A variant of r0d30b0y answer is to use PATINDEX to find within a string...
r0d30b0y 答案的一个变体是使用 PATINDEX 在字符串中查找...
PATINDEX('%'+REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%',@StringToCompare) > 0
Had to use to find Guids within a URL string..
必须使用在 URL 字符串中查找 Guids..
HTH
HTH
Dave
戴夫
回答by Geary M. McIver
Though an older post, just a thought for a quick test ...
虽然是一个较旧的帖子，只是一个快速测试的想法......
SELECT [A].[INPUT],
CAST([A].[INPUT] AS [UNIQUEIDENTIFIER])
FROM (
SELECT '5D944516-98E6-44C5-849F-9C277833C01B' Collate Latin1_General_100_BIN AS [INPUT]
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'
) [A]
WHERE PATINDEX('[^0-9A-F-{}]%', [A].[INPUT]) = 0
回答by Ansonmus
This is a function based on the concept of some earlier comments. This function is very fast.
这是一个基于一些早期评论概念的函数。这个功能非常快。
CREATE FUNCTION [dbo].[IsGuid] (@input varchar(50))
RETURNS bit AS
BEGIN
RETURN
case when @input like '[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]-[0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F][0-9a-fA-F]'
then 1 else 0 end
END
GO
/*
Usage:
select [dbo].[IsGuid]('123') -- Returns 0
select [dbo].[IsGuid]('ebd8aebd-7ea3-439d-a7bc-e009dee0eae0') -- Returns 1
select * from SomeTable where dbo.IsGuid(TableField) = 0 -- Returns table with all non convertable items!
*/
回答by ks1bbk
Use RLIKE for MYSQL
对 MYSQL 使用 RLIKE
SELECT 1 WHERE @StringToCompare
RLIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]');
回答by Villie
I use :
我用 ：
ISNULL(convert(nvarchar(50), userID), 'NULL') = 'NULL'
