Conversion failed when converting from a character string to uniqueidentifier

• DotNetXenon

  Old Hand

  Points: 323

  More actions

  July 28, 2010 at 9:28 am

  #157110

  Table has unique identifier column and newid() was inserted into the table.

  When select query is executed like below,

  select * from table where id = ' d65cafc-1435-45d3-acce-dc464f02c4b1'

  error message is displayed.

  Error : "Conversion failed when converting from a character string to uniqueidentifier"

  Please help

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  July 28, 2010 at 9:35 am

  #1199314

  DECLARE @TABLE AS TABLE(

  id UNIQUEIDENTIFIER)

  INSERT INTO @TABLE

  SELECT Newid()

  SELECT *

  FROM @TABLE

  WHERE CONVERT(VARCHAR(255), id) = 'd65cafc-1435-45d3-acce-dc464f02c4b1'

  How's that?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Lamprey13

  SSCertifiable

  Points: 6210

  More actions

  July 28, 2010 at 2:27 pm

  #1199489

  Two things.

  1. d65cafc-1435-45d3-acce-dc464f02c4b1 is not a proper UNIQUEIDENTIFIER. The first grouping only has 7 characters when there should be 8.

  2. Don't rely on implict conversions. CAST the GUID string to a UNIQUEIDENTIFIER and compare it to your column. Don't CAST the column to a string as SQL cannot seek on the index (assuming there is one).

• palash.veyron

  Valued Member

  Points: 57

  More actions

  September 18, 2015 at 3:06 am

  #1827663

  Ensure that the Column you are Converting OR Casting do not contain BLANK '' Values.