How to Check If a String Is a Uniqueidentifier

How to check if a string is a 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;

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

How to check UNIQUEIDENTIFIER value and replace with select string value if it exist

You could use CASE:

SELECT 
  ans.Id,
  CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER, ans.Value) IS NOT NULL THEN 
            (select TOP 1 col_name from xtable WHERE ... ORDER BY ...)
       ELSE ans.Value
  END AS calculatedVal
FROM [Answers] AS ans;

Or using IIF

SELECT 
  ans.Id,
  IIF(TRY_CONVERT(UNIQUEIDENTIFIER, ans.Value) IS NOT NULL,
      (select TOP 1 col_name from xtable WHERE ... ORDER BY ...)
      ,ans.Value)AS calculatedVal
FROM [Answers] AS ans;

Comparing string with an UNIQUEIDENTIFIER?

Have you tried passing it as a parameter?

SET @SQL_String = 'INSERT INTO #Guids(FoundGuid) SELECT ' + @ColName + ' FROM ' + @TableSchema + '.' + @TableName + ' WHERE ' + @ColName + ' = @GuidArgument';

EXEC sp_executesql @SQL_string,
                   N'@GuidArgument UNIQUEIDENTIFIER',
                   @GuidArgument = @GuidArgument;

Checking if input is GUID or not

You could always use Regex to check the input:

//returns true
Regex.IsMatch(Guid.NewGuid().ToString(), @"(?im)^[{(]?[0-9A-F]{8}[-]?(?:[0-9A-F]{4}[-]?){3}[0-9A-F]{12}[)}]?$") 

or use the built in function like suggested:

Guid g;

if (Guid.TryParse(Guid.NewGuid().ToString(), out g))
{
    return "Success";
}

SQL Server: ISNULL on uniqueidentifier

Since the first argument you are passing isnull is not a literal null, it will determine the return type of that call, a uniqueidentifier in your case. The second argument, '', cannot be cast to this type, hence the error you're getting.

One way around this is just to explicitly check for nulls:

WHERE (@myvar IS NULL AND col1 IS NULL) OR (col1 = @myvar)

Checking for Guid value in nvarchar column

You could create a custom function that tries to validate the data and detect it. I found some thing here for tsql:

http://ask.sqlservercentral.com/questions/23114/best-way-to-check-that-a-stringvarchar-guid-is-val.html

Update:

Check marc_s's answer here too:

How to check if a string is a uniqueidentifier?

Convert string to uniqueidentifier when join is happening

You can simply cast the VARCHAR value to a UNIQUEIDENTIFIER, for example:

CAST('482C44DD-ECFB-4B79-9118-86B681CC51C9' AS UNIQUEIDENTIFIER)

So your JOIN clauses become something like this:

ON command.[Guid] = CAST(calculaion.[CalculationCommandGuid] AS UNIQUEIDENTIFIER)

---

Related Topics