I had an issue today that resulted in a tweet to #sqlhelp:
#sqlhelp See Remarks section: http://ow.ly/Za6HX So how do you strip out 0x000? Suggestions?
As it turns out, when you have a character string in SQL Server that contains character 0x000, it really doesn’t know what to do with it most of the time, especially when you’re dealing with Unicode strings.
I did track down http://sqlsolace.blogspot.com/2014/07/function-dbostripunwantedcharacters.html, but I generally try to avoid calling UDF’s in my queries.
I ended up settling on this, which is based on the fact that REPLACE() works when the data is ASCII…
CASE WHEN LEN(REPLACE(CAST([foo] AS VARCHAR(4000)), CHAR(0), '')) = LEN([foo]) THEN [foo] ELSE [dbo].[strip_char0]([foo], NCHAR(9)) END
My strip_char0 function was mostly like the one from sqlsolace, so I won’t repeat it here. But I did add in a replacement character option (in this example, a tab). And I’m only calling the UDF when I need to, which makes the performance geek in me happy.
My thanks to @SQLSoldier for being responsive on the #sqlhelp hashtag.