SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Binary Data Conversion


Binary Data Conversion

Author
Message
SivaKumar-441284
SivaKumar-441284
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 175
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 :-)
Robert klimes
Robert klimes
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5005 Visits: 3464
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));
go

insert into mcs
values(269,'0x003200360039'),
(8859,'0x0038003800350039'),
(10,'0x00310030'),
(45,'0x00340035');
go

create function dbo.udf_mcstodec(@seg varchar(4))
returns nvarchar(max)
WITH EXECUTE AS CALLER
as
begin
declare @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 int
declare @i int
declare @j int
set @i = 0
set @j = 1
set @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
SivaKumar-441284
SivaKumar-441284
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 175
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 :-)
Robert klimes
Robert klimes
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5005 Visits: 3464
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search