sqlserver如何把float点数字节移位

[班长老六哥]

给我的表，字段类型float类型，数据是
78 56 34 12（78 56 34 12 分别是BCD码,但是存到数据库是浮点类型存储的,我的想法是到数据库后先转化成bcd码
78 56 34 12 ，然后在移位成12 34 56 78 ，再加小数点，但是不知道如何操作）
要转换成float类型
12 34 56. 78 有啥办法吗 求高手帮忙 当然这不是固定的 每次的数据都不一样，但是都是这个规律转换

[唐诗三百首]

不好意思, 没看明白.

[班长老六哥]

引用 1 楼 ap0405140 的回复:

不好意思, 没看明白.

我实现了一部分,下面的查询结果能转化成浮点数吗,转化成浮点数我就实现了要的功能了

with  a3 as
(
select SUBSTRING((select CAST(78563412.0 as varchar(20)) as a1),7,2)as a2,1 as id
),
  b3 as
(
 select SUBSTRING((select CAST(78563412.0 as varchar(20)) as b1),5,2)as b2 ,1 as id
),c3 as
(
select SUBSTRING((select CAST(78563412.0 as varchar(20)) as c1),3,2)as c2 ,1 as id
),d3 as
(
select SUBSTRING((select CAST(78563412.0 as varchar(20)) as d1),1,2)as d2 ,1 as id
)
select e3 from (  select a3.a2,b3.b2,c3.c2,d3.d2,(a2+b2+c2+'.'+d2) as e3 from a3,b3,c3,d3 where a3.id=b3.id) as f2

[Tiger_Zhao]

BCD应该是binary数据啊。

;WITH a(BCD) AS (
    SELECT 0x78563412
),
b(s) AS (
    SELECT SUBSTRING(sys.fn_varbintohexstr(BCD),3,8)
      FROM a
),
c(value) AS (
    SELECT CONVERT(float,
                   SUBSTRING(s,7,2)+
                   SUBSTRING(s,5,2)+
                   SUBSTRING(s,3,2)+'.'+
                   SUBSTRING(s,1,2))
      FROM b
)
SELECT * FROM c

                 value
----------------------
             123456.78