Recently I encountered one error, while converting text datatype to varchar datatype and then adding some string to it to convert into xml. I noticed that the Length of the value is 8000 characters around however when I actually copy and paste the record in notepad and get the length I get only 200 characters. By playing with the value, got to know that the characters from 201 to 8000 were actually some garbage value, which is not visible. Due to this garbage value when I try to append xml node in the end, it does not appear and throws error while converting to xml.
The workaround I used is converted the value to varbinary and then had a look. I was surprised to see all the values from 201 to 8000 as ‘0’ in the binary conversion. Binary takes 2 bytes per character to show the code of that character so instead of 8000 it was 16000 characters length.
The varbinary value I got was ‘0x3C3F786D6C696F6E526571756 65793E3C2F7265616465726B65793E0A20203C 443E0A202020203C6F733E62623C2F6F733E0A202020203C456D61696C3E6B6F7262 E74656C757332383C2F70617373776F72643E0A202020203C64 2F5573657256616C69646174696F6E526571756573743E000000000000000000000000000000000000000000000000000000……..’
Now, I need to ignore such kind of records. Since, when I look into the record from left it looks ok, only when I reverse the value I see that blank value. So, I thought to take Right of 1000 characters from the column and converted to binary. Now, we cannot have any comparison on binary value. The best way I found is to convert into bigint. After converting to bigint, the value returned 0, as the last characters from 201 to 8000 were garbage value. So, my condition was to ignore the records where the 1000 right characters evaluated to 0 after converting to binary and then int.
My condition was
CASE WHEN CAST(CAST(RIGHT(CAST(Columnname AS VARCHAR(MAX)),1000) AS VARBINARY(MAX)) AS BIGINT)= 0 THEN 0 ELSE 1 END = 1
Let me know if anyone has encountered such situation anytime and handled in some different way.