如何检查字符串是否是惟一标识符?

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

对于uniqueidentifier (SQL Server)，是否存在一个与IsDate或IsNumeric相同的版本?或者有什么与(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

12 个解决方案

#1

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

通过TRY_CONVERT(uniqufier, expression)， SQL Server 2012使这一切变得更加容易。

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 UNIQUEIDENTIFIER without complaint or may still end up causing invalid cast errors.

对于以前的SQL Server版本，现有的答案可能会漏掉一些要点，这意味着它们可能不匹配SQL Server实际上会毫无怨言地将字符串强制转换为惟一标识符的字符串，也可能最终导致无效的强制转换错误。

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

SQL Server接受用{}包装的或没有这个的gui。

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.

此外，它忽略了字符串末尾的无关字符。

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

在大多数默认的排序规则下，类似的“[A- za - z0 -9]”最终会匹配字符，如A或E

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 + '}%' 

#2

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]');

#3

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]');

#4

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 Server作为SQL clr组装,那么您可以使用诸如Guid.TryParse()当然是更容易使用比在t - SQL ....

#5

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

戴夫

#6

Like to keep it simple. A GUID has four - in it even, if is just a string

就像保持简单一样。GUID中有四个-，如果只是字符串的话

WHERE column like '%-%-%-%-%'

,列如% - % - % - % - %’

#7

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!

*/

#8

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

#9

You can write your own UDF. This is a simple approximation to avoid the use of a SQL-CLR assembly.

您可以编写自己的UDF。这是一个避免使用SQL-CLR程序集的简单近似。

CREATE FUNCTION dbo.isuniqueidentifier (@ui varchar(50))  
RETURNS bit AS  
BEGIN

RETURN case when
    substring(@ui,9,1)='-' and
    substring(@ui,14,1)='-' and
    substring(@ui,19,1)='-' and
    substring(@ui,24,1)='-' and
    len(@ui) = 36 then 1 else 0 end

END
GO

You can then improve it to check if it´s just about HEX values.

然后您可以改善它检查如果´s十六进制值。

#10

I use :

我使用:

ISNULL(convert(nvarchar(50), userID), 'NULL') = 'NULL'

#11

I had some Test users that were generated with AutoFixture, which uses GUIDs by default for generated fields. My FirstName fields for the users that I need to delete are GUIDs or uniqueidentifiers. That's how I ended up here.

我有一些测试用户是由AutoFixture生成的，它在默认情况下使用GUIDs生成字段。我需要删除的用户的FirstName字段是gui或惟一标识符。这就是我的结局。

I was able to cobble together some of your answers into this.

我能把你的一些答案拼凑在一起。

SELECT UserId FROM [Membership].[UserInfo] Where TRY_CONVERT(uniqueidentifier, FirstName) is not null

从(会员)选择UserId。其中TRY_CONVERT(uniqueidentifier, FirstName)不是空的

#12

Use RLIKE for MYSQL

使用RLIKE MYSQL

SELECT 1 WHERE @StringToCompare
RLIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]');