不清楚你的具体需求,
试试!
DECLARE @STR nvarchar(max)
SET @STR='AAAA,BBBB,CCCC.DDDD,EEEE,FFFF.GGGG,HHHH,KKKK.'
SELECT
substring(@STR,1,charindex('.',@STR)) as '第一句',
substring(@STR,charindex('.',@STR)+1,len(@STR)- charindex('.',@STR) - charindex('.',reverse(substring(@STR,2,len(@STR)-2)))) as '第二句',
reverse(substring(reverse(substring(@STR,1,len(@STR)-1)),0,charindex('.',reverse(substring(@STR,1,len(@STR)-1))))) as '第三句'
/*
第一句 第二句 第三句
----------------- ----------------------------------------
AAAA,BBBB,CCCC. DDDD,EEEE,FFFF. GGGG,HHHH,KKKK(1 row(s) affected)
*/
试试!
DECLARE @STR nvarchar(max)
SET @STR='AAAA,BBBB,CCCC.DDDD,EEEE,FFFF.GGGG,HHHH,KKKK.'
SELECT
substring(@STR,1,charindex('.',@STR)) as '第一句',
substring(@STR,charindex('.',@STR)+1,len(@STR)- charindex('.',@STR) - charindex('.',reverse(substring(@STR,2,len(@STR)-2)))) as '第二句',
reverse(substring(reverse(substring(@STR,1,len(@STR)-1)),0,charindex('.',reverse(substring(@STR,1,len(@STR)-1))))) as '第三句'
/*
第一句 第二句 第三句
----------------- ----------------------------------------
AAAA,BBBB,CCCC. DDDD,EEEE,FFFF. GGGG,HHHH,KKKK(1 row(s) affected)
*/
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (b varchar(1000))
insert into #a
select '如果,需要,然后.如果,需要,然后.'select
replace(a,',',',1')+'2'+b+'3'+reverse(stuff(c,charindex(',',c),1,'4,'))+'5'b
from
(
select
a,left(b,charindex(',',b))b,reverse(right(b,len(b)-charindex(',',b)))c
from
(
select left(b,charindex('.',b))a,right(b,len(b)-charindex('.',b))b from #a
) as t1
) as t2
/*
b
------------------------------------
如果,1需要,1然后.2如果,3需要,4然后.5
*/
SELECT @VAR_CMD=FLD1 FROM #T
DECLARE @INT_I INTSET @INT_I =1
SET @VAR_CMD='SELECT ''' + REPLACE(@VAR_CMD,',',''' AS F1 UNION ALL SELECT ''') + ''''
SET @VAR_CMD='
SELECT RANK() OVER (order by F1),F1 FROM (' + @VAR_CMD + ') AS TAB1 '
PRINT @VAR_CMD
EXECUTE( @VAR_CMD )GODROP TABLE #T
----------------------------
1 A
2 B
3 C
4 D
5 E
6 F
7 G