declare @a datetime = '2011-3-5', @b datetime = '2011-3-7'; with a(Id,Comment,Title) as ( select 1, '2011-3-4,CNCQ,2011-3-6', '重庆' union all select 2, 'CNSH', '上海' union all select 3, '2011-3-6,CNWH,', '武汉' union all select 4, ',CNDL,2011-3-9', '大连' union all select 5, NULL, '未选择' union all select 6, '2011-3-5,,2011-3-6', '未选择' union all select 7, '2011-3-1,CNQD,2011-4-8', '青岛' ), b as ( select *, m = charindex(',',Comment), n = charindex(',',Comment,charindex(',',Comment)+1) from a ) select Id,Comment,Title from b where m<n and left(Comment,m-1)>=@a and nullif(substring(Comment,n+1,99),'')<=@b /* Id Comment Title ---------------------------------------------------- 6 2011-3-5,,2011-3-9 未选择 */
with a(Id,Comment,Title) as
(
select 1, '2011-3-4,CNCQ,2011-3-6', '重庆' union all
select 2, 'CNSH', '上海' union all
select 3, '2011-3-6,CNWH,', '武汉' union all
select 4, ',CNDL,2011-3-9', '大连' union all
select 5, NULL, '未选择' union all
select 6, '2011-3-5,,2011-3-6', '未选择' union all
select 7, '2011-3-1,CNQD,2011-4-8', '青岛'
), b as
(
select *, m = charindex(',',Comment), n = charindex(',',Comment,charindex(',',Comment)+1) from a
)
select Id,Comment,Title from b where m<n and left(Comment,m-1)>=@a and nullif(substring(Comment,n+1,99),'')<=@b
/*
Id Comment Title
----------------------------------------------------
6 2011-3-5,,2011-3-9 未选择
*/