SQL Server 2005+ 使用 CTE 分割字符串

记录一下，效率比旧的字符串分割效率高

ALTERFUNCTION[dbo].[UF_SpliteIntToTable]

(

@StringVARCHAR(8000)

)

RETURNS@TableTABLE(IdINT)

AS

BEGIN

DECLARE@SpliteCharASCHAR(1)=',';

WITHTempTableAS

(

SELECTCAST(SUBSTRING(@String,1,CHARINDEX(@SpliteChar,@String,1)-1)ASINT)ASitem,

SUBSTRING(@String,CHARINDEX(@SpliteChar,@String,1)+1,LEN(@String))+@SpliteCharASlist

UNIONALL

SELECTCAST(SUBSTRING(list,1,CHARINDEX(@SpliteChar,list,1)-1)ASINT)ASitem,

SUBSTRING(list,CHARINDEX(@SpliteChar,list,1)+1,LEN(list))ASlist

FROMTempTable

WHERELEN(TempTable.list)>0

)

INSERTINTO@TableSELECTitemFROMTempTableWHEREitem>0

RETURN

END