How to check if a string is a uniqueidentifier?

Question

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 

Answer 1

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; 

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 accepts GUIDs either wrapped in {} or without this.

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 Ë

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.

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

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