Conversion failed error is showing for uniqueidentifier in SQL query

Question

I have a SQL Server table which contains a column userid of type uniqueidentifier and the column value is 9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E.

When I'm querying like this:

 WHERE userid = '9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E'

The query is running successfully. If I add extra characters at the end of the string it is also working fine like below

WHERE userid = '9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7Eqweqweqwemmmmmmmmmm'

But the problem is when I'm adding extra characters at the start of the string the query is showing error.

WHERE userid = 'A9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E'

The error showing like

Conversion failed when converting from a character string to uniqueidentifier

My question is why the error is showing for only adding character at the start of the string and how to track this error inside a stored procedure

Solution

According to Microsoft documentation:

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. See the Examples section.

That explains why it works fine if you append characters after the 36th position.

When you prepend characters to the guid, you are breaking the formatting rules for the guid and then the conversion fails.

In a stored procedure you can validate the guid by using TRY_CONVERT. It will return NULL if the conversion is not possible:

IF TRY_CONVERT(UNIQUEIDENTIFIER,@userId) IS NULL
   BEGIN
      .... report error ...
   END

TRY_CONVERT is only available from SQL Server 2012. If you need to validate a string before conversion to UNIQUEIDENTIFIER on older versions, you can use the following code:

IF NOT @userId LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%'
    BEGIN
          .... report error ...
    END

Comments

[marc_s]

TRY_CONVERT is a new feature in SQL Server 2012, so it's not available to the OP who is using SQL Server 2008 ....

[tomislav_t]

I have edited the answer to cover SQL Server versions prior to 2012.