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