Using a GUID In The Where Clause

Question

For some reason I'm unable to use comparisons on GUID columns, it does not return any results.

See below, with the WHERE clause set to the exact value of the 'secguid' column, it does not return any results. What's going on?

SELECT * FROM dbMobileFile

SELECT * FROM dbMobileFile WHERE secguid = '3137459D-EFDE-449E-94A3-89345A8580FA'

SELECT * FROM dbMobileFile WHERE secguid LIKE '3137459D-EFDE-449E-94A3-89345A8580FA'

Using LIKE does not work either.

Answer 1

Try this

SELECT      [fileID],
            [fileCOde],
            [filePassword],
            [fileDescription],
            [rowguid],
            [secguid]
FROM        [dbo].[dbMobileFile]
WHERE       CAST(secguid as uniqueidentifier) = CAST('3137459D-EFDE-449E-94A3-89345A8580FA' as uniqueidentifier)

Answer 2

Since you mention that the column is stored as NVARCHAR, its possible that the string has leading or trailing whitespaces, which is why it might not be popping up in the query with the WHERE clause.

You can try this :

SELECT      [fileID],
            [fileCOde],
            [filePassword],
            [fileDescription],
            [rowguid],
            [secguid]
FROM        [dbo].[dbMobileFile]
WHERE       LTRIM(RTRIM(secguid)) = '3137459D-EFDE-449E-94A3-89345A8580FA'

which should show you the result as leading and trailing whitespaces are eliminated in the WHERE clause.

Also, in case you want to make use of the LIKE operator, you can write your query as :

SELECT      [fileID],
            [fileCOde],
            [filePassword],
            [fileDescription],
            [rowguid],
            [secguid]
FROM        [dbo].[dbMobileFile]
WHERE       secguid LIKE '%3137459D-EFDE-449E-94A3-89345A8580FA%'

Hope this helps!!!