{"id":108,"date":"2012-07-04T10:17:30","date_gmt":"2012-07-04T14:17:30","guid":{"rendered":"http:\/\/blogs.visigo.com\/chriscoulson\/?p=108"},"modified":"2012-07-04T10:17:30","modified_gmt":"2012-07-04T14:17:30","slug":"removing-null-characters-0x00-from-values-in-sql-server","status":"publish","type":"post","link":"https:\/\/blogs.visigo.com\/chriscoulson\/removing-null-characters-0x00-from-values-in-sql-server\/","title":{"rendered":"Removing Null Characters (0x00) From Values in SQL Server"},"content":{"rendered":"

I recently came across a problem while crawling a BCS data source in SharePoint 2010. Most of the 130,000 records were getting indexed, but I was getting an error on 25 records:<\/p>\n

Error while crawling LOB contents. ( ‘.’, hexadecimal value 0x00, is an invalid character. Line 25, position 10. )<\/p><\/blockquote>\n

When I first took a look at the offending records in the database I couldn’t see any actual problems. \u00a0The issue was that the null character in the data was being treated as a string terminator, so it was not being returned with a standard select query. I was finally able to detect the null values by casting the suspected offending column to varbinary:<\/p>\n

\r\n\r\nSELECT CAST(TheColumn AS varbinary(max)) FROM TheTable WHERE TheID = 123\r\n\r\n<\/pre>\n
Casting to varbinary returns the character values in the column as\u00a0hexadecimal. \u00a0Once the values were converted to hexadecimal I could see the null values represented as 0000 (In my case the offending column was nvarchar, if your column is varchar you’ll be looking for 00).<\/p>\n
Now that I could see the problem, I needed a way to quickly and easily remove the characters from any records that contained them in the database. I found a post on StackOverflow that finally pointed me in the right direction:<\/p>\n
http:\/\/stackoverflow.com\/questions\/2828333\/what-is-the-null-character-literal-in-tsql<\/a><\/p>\n
Simply searching for char(0) didn’t return any values in my case. However if the data is cast from nvarchar to varchar and the collation is set to ‘SQL_Latin1_General_CP1_CI_AS’\u00a0it does work. Note that the cast to varchar was required for the null character to be detected. Luckily in my case the offending records didn’t contain Unicode characters. \u00a0Here’s what I finally ended up with to search for and remove the null characters:<\/p>\n
\r\n\r\nUPDATE TheTable\r\nSET TheColumn = REPLACE(CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS, CHAR(0), '')\r\nWHERE CAST(TheColumn as varchar(max)) COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + CHAR(0) + '%'\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
I recently came across a problem while crawling a BCS data source in SharePoint 2010. Most of the 130,000 records were getting indexed, but I was getting an error on 25 records: Error while crawling LOB contents. ( ‘.’, hexadecimal value 0x00, is an invalid character. Line 25, position 10. ) When I first took […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[244,245,247,253,246,248,249,250,242,243,251,22,24,18,252],"class_list":["post-108","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-0x00","tag-0x000","tag-bcs","tag-char0","tag-collation","tag-crawl","tag-index","tag-invalid-character","tag-null-character","tag-null-value","tag-nvarchar","tag-sharepoint-2","tag-sharepoint-2010","tag-sql-server-2","tag-varbinary"],"_links":{"self":[{"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/posts\/108","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/comments?post=108"}],"version-history":[{"count":1,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/posts\/108\/revisions"}],"predecessor-version":[{"id":109,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/posts\/108\/revisions\/109"}],"wp:attachment":[{"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/media?parent=108"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/categories?post=108"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.visigo.com\/chriscoulson\/wp-json\/wp\/v2\/tags?post=108"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}