字符串拆分
有序号字段
942
946
941
940/8
939/945
查询结果
942
946
941
940
8
939
945
sql 2005数据库，有什么高效的语句么
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
CREATE TABLE #temp(NAME VARCHAR(100))
insert #temp
select '942' union all
select '946' union all
select '941' union all
select '940/8' union all
select '939/945'
SELECT b.name
FROM
(SELECT [name]=CONVERT(XML, '<root><v>'+replace([name],'/','</v><v>')+'</v></root>') FROM #temp) a
OUTER APPLY
(SELECT [name] = C.v.value('.','NVARCHAR(MAX)') FROM a.[name].nodes('/root/v') C(v)) b
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-15 14:39:20
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([a] varchar(7))
insert [huang]
select '942' union all
select '946' union all
select '941' union all
select '940/8' union all
select '939/945'
--------------开始查询--------------------------
SELECT
SUBSTRING([a],number,CHARINDEX('/',[a]+'/',number)-number) as [a]
from
[huang] a,master..spt_values
where
number >=1 and number<len([a])
and type='p'
and substring('/'+[a],number,1)='/'