最近实施项目中，遇到需要在sqlserver中截取字符串的问题，但研究半天也没研究出来，现发上来请求高手指点，字符串格式如下：
截取后的效果为：
也就是按“&”来截取！ 恳请高手指点，如有代码更好，非常感谢
------解决方案--------------------
declare @str varchar(1000)
select @str='2255&2297&-0.4500&0.3300'
select substring(a.s,b.number,charindex('&',a.s+'&',b.number)-b.number) 'str'
from (select @str 's') a
inner join master.dbo.spt_values b
on b.[type]='P' and b.number between 1 and len(a.s)
and substring('&'+a.s,b.number,1)='&'
/*
str
----------------------------
2255
2297
-0.4500
0.3300
(4 row(s) affected)
*/
------解决方案--------------------
DECLARE @Str VARCHAR(200)='2255&2297&-0.4500&0.3300'
SELECT ROW_NUMBER() OVER(ORDER BY @@SERVERNAME) AS ID
,t.c.value('.','Varchar(10)') AS col from
(SELECT CAST('<x>'+REPLACE(@Str,'&','</x><x>')+'</x>' AS XML ).query('.') AS name) AS a
CROSS APPLY a.name.nodes('/x') T(c)