表里有一字段,类型为varbinary,
现需要按"%016I64x%02x" 格式分割此字段字串,如:
0x48FF2BC8030100000048FF2BC80301000101
48FF2C93050100010248FF2CBE0501000303
48FF2CC1050100040448FF2CC40501000505
490039C20501000006490039C60501000107
4902B29D0502276208则分割成
CharSerial slot
48FF2BC803010000 00
48FF2BC803010001 01
48FF2C9305010001 02
48FF2CBE05010003 03
48FF2CC105010004 04
48FF2CC405010005 05
490039C205010000 06
490039C605010001 07
4902B29D05022762 08
现需要按"%016I64x%02x" 格式分割此字段字串,如:
0x48FF2BC8030100000048FF2BC80301000101
48FF2C93050100010248FF2CBE0501000303
48FF2CC1050100040448FF2CC40501000505
490039C20501000006490039C60501000107
4902B29D0502276208则分割成
CharSerial slot
48FF2BC803010000 00
48FF2BC803010001 01
48FF2C9305010001 02
48FF2CBE05010003 03
48FF2CC105010004 04
48FF2CC405010005 05
490039C205010000 06
490039C605010001 07
4902B29D05022762 08
select substring(字段,charindex(字段,4),16) as CharSerial,right(字段,2) as slot from 表
GO
SET ANSI_NULLS OFF
GOALTER function StringSplit(@c varbinary(2000))
returns @t table(Prototype varbinary(4),[Level] varbinary(1),[Exp] varbinary(4))
as
begin
declare @start int
declare @length int
set @length = datalength(@c)
set @start = 1
while(@start< @length)
begin
insert @t(Prototype,[Level],[Exp]) values (substring(@c,@start,4),substring(@c,@start+4,1),substring(@c,@start+5,4))
set @start = @start+9
end
return
end GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
select * from StringSplit(0x0C019C4101000000000C019C4201000000000C019C4301000000000C019C4401000000000C019C4501000000000C019C4601000000000C019C4701000000000C019C480100000000)