CODEWITHSUNDEEP
sqlsql-server
W.H
W.H

Reputation: 197

Using a GUID In The Where Clause

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.

enter image description here

enter image description here

Upvotes: 11

Views: 29733

Answers (6)

Adithya
Adithya

Reputation: 285

See inside the database that guid value stored as 32 hex digits:00000000000000000000000000000000 so if we search by 32 hex digits separated by hyphens: 00000000-0000-0000-0000-000000000000, it's not get any output

Upvotes: -1

statler
statler

Reputation: 1381

The accepted answer works, but it is a bit verbose and probably not the intended way to do this. UniqueIdentifier is qualified by {}, so the following is the easiest;

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

Upvotes: -1

kaspur
kaspur

Reputation: 707

I've had this problem with a corrupt database. Some GUIDs be contained in the WHERE clause, with other GUIDS on the same table would not return results.

Turns out that database had Index issues. Run DBCC to make sure your database isn't corrupt.

Upvotes: -1

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

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

Upvotes: 2

Winnie Lin
Winnie Lin

Reputation: 5

Try this:

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

Use parentheses to enclose GUID string LIKE ('GUID')

Upvotes: -3

Rahul
Rahul

Reputation: 209

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)

Upvotes: 11

Related Questions