Sql-server – Unique Identifier with Extra Characters Still Matching in Select

sql serversql-server-2012t-sql

We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.

For example:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8'

returns the row with uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

But if you run:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'

it also returns the row with the uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

SQL Server seems to ignore all characters beyond the 36 when doing its selects. Is this a bug/feature or something that can configured?

It's not a massive issue as we have validation on the front end for the length but it doesn't seem correct behaviour to me.

Best Answer

The implicit conversion also works if the value is enclosed in curly brackets {...}.

If you add those in the query the implicit conversion will fail if the original value is too long because the last } ends up in the wrong place.

select * 
from some_table 
where uuid = '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8'+'}'

If you try the convert 

SELECT CONVERT(UNIQUEIDENTIFIER, '{'+'7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'+'}');

you get

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.
Related Question