Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Binary Data Conversion Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, June 09, 2009 5:15 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, July 02, 2013 6:45 AM Points: 51, Visits: 169
 How do I convert binary data which is in MCS (Multinational Character Set) format?consider the binary data '0x00310030' is equivalent to 10 (decimal).How do I use Select query to retrieve this data?. The expected result is 10.Sivakumar Sivakumar
Post #731319
 Posted Tuesday, June 09, 2009 10:00 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 11:08 AM Points: 1,013, Visits: 2,156
 this probably isn't the most elegant of solutions and without any test data or table structures i came up with what could be a starting point for you. I did not have any MCS binary data so to use following method you will have to convert binary to nvarchar. Also take a look at the link following link on how to post to get the best help.http://www.sqlservercentral.com/articles/Best+Practices/61537/`create table mcs(id int,bin varchar(max));goinsert into mcsvalues(269,'0x003200360039'),(8859,'0x0038003800350039'),(10,'0x00310030'),(45,'0x00340035');gocreate function dbo.udf_mcstodec(@seg varchar(4))returns nvarchar(max)WITH EXECUTE AS CALLERasbegindeclare @val nvarchar(max) set @val = case when @seg = '0030' then '0' when @seg = '0031' then '1' when @seg = '0032' then '2' when @seg = '0033' then '3' when @seg = '0034' then '4' when @seg = '0035' then '5' when @seg = '0036' then '6' when @seg = '0037' then '7' when @seg = '0038' then '8' when @seg = '0039' then '9' end return(@val) end;go declare @cnt intdeclare @i intdeclare @j intset @i = 0set @j = 1set @cnt = (select max(LEN(SUBSTRING(bin,3,len(bin)))/4) from mcs)while @i < @cnt begin if @i = 0 begin select @i+1 as seg,substring(SUBSTRING(bin,3,len(bin)),@j,4) as val,bin into #tmp from mcs set @j = @j+4 set @i= @i+1 end else begin insert into #tmp select @i+1 as seg,substring(SUBSTRING(bin,3,len(bin)),@j,4) as val,bin from mcs set @j = @j+4 set @i= @i+1 end end select bin,cast(isnull(dbo.udf_mcstodec([1]),'')+isnull(dbo.udf_mcstodec([2]),'')+isnull(dbo.udf_mcstodec([3]),'')+isnull(dbo.udf_mcstodec([4]),'') as decimal(10,0)) as dec from #tmp pivot (max(val) for seg in ([1],[2],[3],[4]))as p drop table #tmp` Bob-----------------------------------------------------------------------------How to post to get the best help
Post #731605
 Posted Wednesday, June 10, 2009 12:43 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, July 02, 2013 6:45 AM Points: 51, Visits: 169
 Hi Robert klimes, Thanks to your reply. This was my post. I understood how I get best help from people. The code what you had sent is giving expected result. Sivakumar
Post #732051
 Posted Wednesday, June 10, 2009 9:17 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 11:08 AM Points: 1,013, Visits: 2,156
 good to hear it helps. But keep in mind that the code I provided will only convert 0-9 from MCS to decimal and will only show results for up to four values. If you have any MCS data that is longer then 16 characters or any of those characters do not fall into the numeric set then your result will be incorrect. Bob-----------------------------------------------------------------------------How to post to get the best help
Post #732381

 Permissions