How to check if a string is a uniqueidentifier?

Sql Problem Overview

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 Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

Solution 3 - Sql

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:

SELECT something 
  FROM table1 
 WHERE column1 LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]');

Solution 4 - Sql

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....

Solution 5 - Sql

A variant of r0d30b0y answer is to use PATINDEX to find within a string...

PATINDEX('%'+REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%',@StringToCompare) > 0

Had to use to find Guids within a URL string..

HTH

Dave

Solution 6 - Sql

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

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

Solution 7 - Sql

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

Solution 8 - Sql

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!

*/

Solution 9 - Sql

DECLARE @guid_string nvarchar(256) = 'ACE79678-61D1-46E6-93EC-893AD559CC78'

SELECT
	CASE WHEN @guid_string LIKE '________-____-____-____-____________'
	THEN CONVERT(uniqueidentifier, @guid_string)
	ELSE NULL
END

Solution 10 - Sql

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

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.

Solution 11 - Sql

I use :

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

Solution 12 - Sql

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.

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

Solution 13 - Sql

Use RLIKE for MYSQL

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

Solution 14 - Sql

In a simplest scenario. When you sure that given string can`t contain 4 '-' signs.

SELECT * FROM City WHERE Name LIKE('%-%-%-%-%')

Content Type	Original Author	Original Content on Stackoverflow
Question	Benoittr	View Question on Stackoverflow
Solution 1 - Sql	Martin Smith	View Answer on Stackoverflow
Solution 2 - Sql	r0d30b0y	View Answer on Stackoverflow
Solution 3 - Sql	onedaywhen	View Answer on Stackoverflow
Solution 4 - Sql	marc_s	View Answer on Stackoverflow
Solution 5 - Sql	Dave	View Answer on Stackoverflow
Solution 6 - Sql	Mike	View Answer on Stackoverflow
Solution 7 - Sql	Geary M. McIver	View Answer on Stackoverflow
Solution 8 - Sql	Ansonmus	View Answer on Stackoverflow
Solution 9 - Sql	NotoriousPyro	View Answer on Stackoverflow
Solution 10 - Sql	pcofre	View Answer on Stackoverflow
Solution 11 - Sql	Villie	View Answer on Stackoverflow
Solution 12 - Sql	Don Rolling	View Answer on Stackoverflow
Solution 13 - Sql	ks1bbk	View Answer on Stackoverflow
Solution 14 - Sql	Vlad	View Answer on Stackoverflow