WITH a1 (answer) AS ( SELECT '942' UNION all SELECT '946' UNION all SELECT '941' UNION all SELECT '940/8' UNION all SELECT '939/945' ) SELECT SUBSTRING(a.answer,b.number,CHARINDEX('/',a.answer+'/',b.number)-b.number) FROM a1 a JOIN master..spt_values b ON b.number<=LEN(a.answer) AND SUBSTRING('/'+a.answer,b.number,1)='/' AND b.type='P'
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)='/'
(
SELECT '942' UNION all
SELECT '946' UNION all
SELECT '941' UNION all
SELECT '940/8' UNION all
SELECT '939/945'
)
SELECT SUBSTRING(a.answer,b.number,CHARINDEX('/',a.answer+'/',b.number)-b.number)
FROM a1 a JOIN master..spt_values b ON
b.number<=LEN(a.answer) AND SUBSTRING('/'+a.answer,b.number,1)='/' AND b.type='P'
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)='/'