sql中多层分割字符串问题

怎么将字符串
275-2-0-1001512-0-11.8000+275-2-1-1001512-1001370--+275-2-2-1001512-1001358--+275-3-0+1002316-0-48.000+275-3-1-1002316-1004365--等数据，
按照“+”分开这个需要循环，然后在按照“-”分开将每条数据每个字段截取出来插入到一个表，有些字段是空值就是--之间没数据，但也要分割只是插入的时候赋值个空，插入数据库后效果
表中的log，item字段数据是在sql中处理生成的，
就是说如果type字段为0item字段从0开始自增，log从1开始
下条数据如果type不等于0但foodid跟上条相等item就是1，log 也是上调的log值
如果type为0，如果foodid跟上条不相等item就要从0开始自增，log比上回大1
id log item foodid type price
275 2 0 1001512 0 11.8000
275 2 1 1001512 1001370
275 2 2 1001512 1001358
275 3 0 1002316 0 48.0000
275 3 1 1002316 1004365

整体说就是我怎样将字符串分割按照+，在按照-，插入到数据表，控制好log,和item 的值根据type

求一个函数？？？？？

 

/*
标题：简单数据拆分(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
描述：

有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc
*/

--1. 旧的解决方法(sql server 2000)

create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go

--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b

SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','

DROP TABLE #

--方法2.如果数据量小，可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number)
from tb a join master..spt_values  b
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','

--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go

--方法1.使用xml完成
SELECT A.id, B.value FROM
(
  SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
  SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
) B

--方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)


DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/

 

create table tb(value varchar(100))
insert into tb values('275-2-0-1001512-0-11.8000+275-2-1-1001512-1001370--+275-2-2-1001512-1001358--')
go

select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
  select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
  from tb a join master..spt_values  b
  on b.type='p' and b.number between 1 and len(a.value)
  where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values  b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
/*
a_num       b_num       value                                                                                               
----------- ----------- ----------------------------------------------------------------------------------------------------
1           1           275
1           5           2
1           7           0
1           9           1001512
1           17          0
1           19          11.8000
27          1           275
27          5           2
27          7           1
27          9           1001512
27          17          1001370
27          25         
53          1           275
53          5           2
53          7           2
53          9           1001512
53          17          1001358
53          25         

（所影响的行数为 18 行）
*/

select max(case px when 1 then value end) id,
       max(case px when 2 then value end) [log],
       max(case px when 3 then value end) [item],
       max(case px when 4 then value end) [foodid],
       max(case px when 5 then value end) [type],
       max(case px when 6 then value end) [price]
from
(
select m.* , px = (select count(1) from
(
select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
  select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
  from tb a join master..spt_values  b
  on b.type='p' and b.number between 1 and len(a.value)
  where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values  b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
) n where n.a_num = m.a_num and n.b_num < m.b_num) + 1 from
(
select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
  select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
  from tb a join master..spt_values  b
  on b.type='p' and b.number between 1 and len(a.value)
  where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values  b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
) m
) k
group by a_num
/*
id                                                                                                   log                                                                                                  item                                                                                                 foodid                                                                                               type                                                                                                 price                                                                                               
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
275                                                                                                  2                                                                                                    0                                                                                                    1001512                                                                                              0                                                                                                    11.8000
275                                                                                                  2                                                                                                    1                                                                                                    1001512                                                                                              1001370                                                                                             
275                                                                                                  2                                                                                                    2                                                                                                    1001512                                                                                              1001358                                                                                             

（所影响的行数为 3 行）
*/

drop table tb