Guid with extra characters issue

Question

I have a table named Student, contain a column StudentId as GUID, so I used the Uniqueidentifier datatype for that.

If I want to get particular record, I get the result by the below query:

SELECT * FROM Student WHERE StudentId = '919C3BF9-B081-458C-897D-C0B3FF56AF73'

It returns the expected result. But in case if I mistakenly add any extra characters in the end also, it returns the same result. Like the below query:

SELECT * FROM Student WHERE StudentId = '919C3BF9-B081-458C-897D-C0B3FF56AF73xyz'

If I pass the extra characters in the end of GUID, why it is not consider as invalid GUID? and return the same result?

Zinc

rather user string compare in sql – Counterglow

Answer 1

GUID is 16 bytes, so from the given 919C3BF9-B081-458C-897D-C0B3FF56AF73

91 is 1st byte
9C is 2nd byte
3B is 3rd byte
F9 is 4th byte
..
..
..
..
56 is 14th byte
AF is 15th byte
73 is 16th byte

Parsing of 919C3BF9-B081-458C-897D-C0B3FF56AF73xyz completes before the xyz.

So the characters are entered after the 16th byte, won't be consider.

But if you add any extra characters in the front, it won't consider as valid GUID.

Moreover, when you query with GUID, use the code in between the {}.

SELECT * FROM Student 
WHERE StudentId = '{919C3BF9-B081-458C-897D-C0B3FF56AF73}'

Vegetarianism

To add on to this answer, a uniqueidentifier data type has a higher data type precedence than varchar so the varchar literal is implicitly converted to uniqueidentifier. The parsing is apparently more lax without the curly brace enclosure, truncating the extraneous characters in the literal. – Loy

Extra characters within the curly braces result in an error, however extra chars outside {} are ignored and truncated. SELECT CONVERT(uniqueidentifier ,'{919C3BF9-B081-458C-897D-C0B3FF56AF73}EXTRA') SELECT CONVERT(uniqueidentifier ,'{919C3BF9-B081-458C-897D-C0B3FF56AF73EXTRA}') – Piazza

Thanks for introducing the {} when using with guid! – Zinc

Answer 2

As stated from the documentation:

The following example demonstrates the truncation of data when the value is too long for the data type being converted to. Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.

DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;

Here is the result set.

String                                       TruncatedValue
-------------------------------------------- ------------------------------------
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong    0E984725-C51C-4BF4-9960-E1C80E27ABA0

(1 row(s) affected)

Cyndie

msdn even has a similar example detailing the same – Piazza

perhaps add this example to demonstrate: SELECT CONVERT(UNIQUEIDENTIFIER, '919C3BF9-B081-458C-897D-C0B3FF56AF73-This-is-extra') - produces: 919C3BF9-B081-458C-897D-C0B3FF56AF73 – Knossos