Now a followup question. This is is very useful and I am trying to figure out how to do this passing in the table and column names as variable

I think I need to build the expression as a string and then execute it, as just subsituting @colname for CID in the code below doesn't work (just returns the value of @colname for all rows)

Since the string contains single quotes, I tried escaping each single quote with another single quote. Tried using Double Quote around the string I'm building. Not sure how to go about this

DECLARE @colname VARCHAR(100) = 'cid'
DECLARE @tblname VARCHAR(100) = 'caccount_credits'
DECLARE @str VARCHAR(max) = ''

SELECT @str = @str + 'SELECT CASE WHEN NOT (' + @colname + ' LIKE expression + '  -- and here is where I don't know what to put in so '%' renders correctly in the result

????

SELECT CASE
         WHEN NOT (Cid LIKE expression + '%' OR Cid LIKE '{' + expression + '}%') THEN Cid
       END
FROM   @test T
       CROSS APPLY (SELECT REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]') COLLATE Latin1_General_BIN) C2(expression)
WHERE  NOT (Cid LIKE expression + '%' OR Cid LIKE '{' + expression + '}%')

>>I have many many column that are char(36) imported from VFP that have been constructed to be cast as UID.
>>
>>For the most part they convert fine when I
>>
>>ALTER TABLE tblname
>>ALTER COLUMN colname Uniqueidentifier
>>GO
>>
>>However on the four or five that fail, all I get is
>>
>>Conversion failed when converting from a character string to uniqueidentifier.
>>
>>but there are half a million rows, so I don't know where my upsizer is failing to catch a badly formed GUID(36)
>>
>>Is there a tsql syntax that would be something like (forgive the bad pseudocode but you get the idea)
>>
>>SELECT success = CAST(cid as Uniqueidentifier) from ACCOUNT_CREDITS where success = false
>>
>>I need to know which row it chokes on. Could probably write an iteration but seems there must be a set based way to do this.
>>
>>TIA
>
>http://stackoverflow.com/questions/4649317/how-to-check-if-a-string-is-a-uniqueidentifier
>
>

>DECLARE @Test TABLE (Cid char(36))
>
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>INSERT INTO @test VALUES ('adfasdfadfasdfasdfasdfas')
>INSERT INTO @test VALUES ('63F4B1CD-80DD-4F96-B5EF-520A4FDADA63')
>
>SELECT CASE
>         WHEN NOT (Cid LIKE expression + '%' OR Cid LIKE '{' + expression + '}%') THEN Cid
>       END
>FROM   @test T
>       CROSS APPLY (SELECT REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]') COLLATE Latin1_General_BIN) C2(expression)
>WHERE  NOT (Cid LIKE expression + '%' OR Cid LIKE '{' + expression + '}%')
>