Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

I have around 6+ years of experience in SQL Server development and in Business Intelligence area. I am working as Datawarehouse developer. My blog space includes all technical areas related to SQL Server and MSBI (SSIS, SSRS and SSAS).
Browse by Tags · View All
SSIS 7
xml 3
dynamic 3
Union All 3
SSRS 2
Data flow task 2
Buffer 2
Time 2
Date 2
varchar(max) 1

Archive · View All
February 2013 3
September 2009 3
June 2013 2
January 2013 2
November 2012 2
July 2012 2
July 2009 2
April 2013 1
December 2012 1
February 2010 1

Text Datatype conversion

Apr 26 2013 12:00AM by Divya Agrawal Shah   

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.

Tags: text dataype, xml,conversion, length, varchar(max)


Divya Agrawal Shah
89 · 2% · 629
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Text Datatype conversion" rated 5 out of 5 by 1 readers
Text Datatype conversion , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]