June 9, 2009 at 5:15 am
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 🙂
June 9, 2009 at 10:00 am
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/[/url]
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-2 int
set @i = 0
set @j-2 = 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 @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 @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
June 10, 2009 at 12:43 am
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 🙂
June 10, 2009 at 9:17 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply