Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Binary Data Conversion Expand / Collapse
Author
Message
Posted Tuesday, June 9, 2009 5:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:57 AM
Points: 52, Visits: 173
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 9, 2009 10:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 1,342, Visits: 2,512
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
Post #731605
Posted Wednesday, June 10, 2009 12:43 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:57 AM
Points: 52, Visits: 173
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 1,342, Visits: 2,512
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse