Updating Guid Field by Converting CharacterString to UniqueIdentifier - Which row is bad?

Question

Updating Guid Field by Converting CharacterString to UniqueIdentifier - Which row is bad?

JimiJett

SSChasing Mays

Points: 647
More actions
October 21, 2011 at 11:58 am

#250584

Hi All,
I am needing to update a field in a table and pull a substring from another field. The field to be updated is a GUID.
I am pulling the filename from a path:
\\vmamna1hx18M4z.nc.us.bank-dns.com\aosimages$\PowerTrackFreight\2010.7.6\14.30\6b8d7ed2-8618-45c2-8d23-f4a4b37a7777.TIF
Supposedly all the names are valid guids. ie. validguid.tif.
The following update produces a 'Error Converting Character String to UniqueIdentifier'.
UPDATE
img
SET
DocumentId =
CASE
WHEN CHARINDEX('.TIF',ImagePath) > 0
THEN CONVERT(NVARCHAR(36),SUBSTRING(ImagePath,CHARINDEX('.TIF',ImagePath)-36,36)) -- REPLACE(RIGHT(ImagePath,40),'.TIF','')
--ELSE NEWID()
END
FROM
dbo.DE_ImageDetail img
I know that there are bad records in table, just how to figure which ones are making fail?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Man I dislike uniqueidentifier. It is nothing but a pita. Can you store your data in a varchar(36) instead of a uniqueidentifier column?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JimiJett SSChasing Mays Points: 647 More actions · Answer 2

Thanks for reply but it has to be a uniqueidentifier.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Is this a one time thing or a repeatable process? I am guessing it is something you need to repeat.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JimiJett SSChasing Mays Points: 647 More actions · Answer 4

Once for now to populate values in table, but might need to be repeatable. I figured out a two step process to do by selecting into a table and then comparing.

JimiJett SSChasing Mays Points: 647 More actions · Answer 5

Ok all, I wrote a stored procedure to return the rows where a string is a bad guid.

Just pass in DBName, TableName, FieldToTest if contents are guid, KeyField (underlying ID of record that is bad) and it returns those rows from the base table.

I could have written the code to correct data where it returns a valid guid, but I think at times you would want to identify the exceptions without blindly fixing.

I used global ## tables b/c of the dynamic sql.

Here it is:

ALTER PROCEDURE UT_SP_IsStringGuid

@DBName varchar(25),

@TableName varchar(50),

@TestField varchar(255),

@KeyField varchar(50)

AS

/*

--EXEC UT_SP_IsStringGuid 'DataEntry','DE_ImageDetail','ImagePath','ImageDetailID'

DECLARE

@DBName varchar(25),

@TableName varchar(50),

@TestField varchar(255),

@KeyField varchar(50)

SET @DBName = 'DataEntry'

SET @TableName = 'DE_ImageDetail'

SET @TestField = 'ImagePath'

SET @KeyField = 'ImageDetailID'

*/

DECLARE

@SelectQuery varchar(500)

SET @SelectQuery = ''

SET @SelectQuery = @SelectQuery + 'USE ' + @DBName

SET @SelectQuery = @SelectQuery + ' SELECT ' + @KeyField + ' AS KeyField, CONVERT(NVARCHAR(36),SUBSTRING(' + @TestField + ',CHARINDEX(''.TIF'', ' + @TestField + ')-36,36)) AS TestStringToGuid'

SET @SelectQuery = @SelectQuery + ' INTO ##tmp_IsItGuid_Select FROM ' + @DBName + '.dbo.' + @TableName + ' WITH (NOLOCK)'

EXEC(@SelectQuery)

SET @SelectQuery = ''

SET @SelectQuery = @SelectQuery + 'SELECT KeyField, TestStringToGuid, '

SET @SelectQuery = @SelectQuery + ' CASE WHEN TestStringToGuid LIKE REPLICATE(''[0-9A-F]'',8)+''-''+REPLICATE(REPLICATE(''[0-9A-F]'',4)+''-'',3)+REPLICATE(''[0-9A-F]'',12)'

SET @SelectQuery = @SelectQuery + ' THEN ''YES'' ELSE ''NO'' END AS IsGuid '

SET @SelectQuery = @SelectQuery + ' INTO ##tmp_IsItGuid_Compare '

SET @SelectQuery = @SelectQuery + ' FROM ##tmp_IsItGuid_Select '

EXEC(@SelectQuery)

SET @SelectQuery = ''

SET @SelectQuery = @SelectQuery + 'SELECT tmp.TestStringToGuid, base.* FROM ' + @DBName + '.dbo.' + @TableName + ' base WITH (NOLOCK)'

SET @SelectQuery = @SelectQuery + ' INNER JOIN ##tmp_IsItGuid_Compare tmp '

SET @SelectQuery = @SelectQuery + 'ON base.' + @KeyField + ' = tmp.KeyField '

SET @SelectQuery = @SelectQuery + 'WHERE tmp.IsGuid = ''NO'''

EXEC(@SelectQuery)

DROP TABLE ##tmp_IsItGuid_Select

DROP TABLE ##tmp_IsItGuid_Compare

RETURN

Thanks for reading.