How to Join to a uniqueidentifier column with value is not a Guid RRS feed

  • Question

  • User-1188570427 posted

    I have a column that has to be a varchar(255) in my databsae because some of the values might not be guids even though most probably will.  How can I join on that column to pull the user as long as the value is a GUID in the varchar(255) column? 

    For example, sometimes the information was loaded by the system so the value is "SYSTEM_LOAD" instead of a guid.

    It gives me the following error because of "SYSTEM_LOAD" I assume:

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


    Monday, December 14, 2015 1:00 PM

All replies

  • User77042963 posted
    create table test (id int identity(1,1), col1 varchar(255)  )
    Insert into test values(try_convert(varchar(36), newid())),('SYSTEM_LOAD')
    select * from test
    Where try_convert(uniqueidentifier, col1)  is not null
    drop table test 

    Monday, December 14, 2015 4:56 PM