SQL Server 2008 Converting VARCHAR(50) to Uniqueidentifier

Question

Hi!

I'm trying to convert a varchar(50) column to a uniqueidentifier, but this error keeps popping up all the time, and i don't know why:

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

The data in the column is currently a valid uniqueidentifier.

What's the proper way of doing what I want?

Thanks

Answer 1

Do you have any columns containing empty strings? I.e. NOT NULL, string length = 0.

Or do you have any GUIDs with non-standard characters? I.e. not 0-9, A-F?

We have some non-standard GUIDs in my application that were created before I inherited it...

EDIT:

For future help, this script can help you find any rows that aren't valid:

SELECT    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0'), COUNT(*)
FROM TABLE
GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0')

Any rows that have invalid Guids will show up, and can be found by:

SELECT    *, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0')
FROM TABLE
WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0') != '00000000-0000-0000-0000-0000000000'

Answer 2

Thanks for the replies. Out of 5000 rows I had one containing a non hex-valid character.

Answer 3

hi i too face same error but i cant able to get solution ??