set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[f_Split]



(



 @SplitString ntext, -- 如果要传入NText类型，下面需要相应的修改，注释行为NText下同



 @Separator nvarchar(2) = ','-- NVarChar(2) = N','



)



RETURNS @SplitStringsTable TABLE



(



 [id] int identity(1,1),



 [value] nvarchar(4000) -- NVarChar(4000)



)



AS



BEGIN



    DECLARE @CurrentIndex int;



    DECLARE @NextIndex int;



    DECLARE @ReturnText nvarchar(4000);-- NVarChar(4000)



    SELECT @CurrentIndex=1;



    WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2



    BEGIN



        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);



        IF(@NextIndex=0 OR @NextIndex IS NULL)



            SELECT @NextIndex=datalength(@SplitString)+1;--DATALENGTH(@SplitString)/2

        



        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);



        INSERT INTO @SplitStringsTable([value])



        VALUES(@ReturnText);       



        SELECT @CurrentIndex=@NextIndex+1;



    END



    RETURN;



END

select * from dbo.f_Split('11910BE3-FDC8-4F42-B6B9-3DF55938FCF1,E9221AF7-CFB8-428F-9C2F-D5D6A7A815D6,8E7A9B4C-0823-4402-982E-48D9AB51783D,B5180191-AE4A-440D-8BAB-54BC3B8AE15C,F145B0D0-13E4-45EF-8299-8ED88689F397,6FB9FA54-D7BB-46F5-829E-4A3488B6A756,34E7A8F5-2ECE-4FBC-B465-AB52697AF1E8,B735FFA6-C6D5-43C2-8B34-D7FDEED99CED,409753B4-DE91-4957-B3E7-9FD12230C3C5,ED9B7030-0784-462B-9D68-FD0A576A5462,D0FA63CB-0CDF-41BB-9034-49BFA1075C13,B4491941-E272-4866-AA4B-33E78B553FBE,89F34774-7364-4FE7-BA98-34899682A949,DB52F8C5-EED3-451B-A451-E4F08A278122,D397CE8D-C2A0-45B7-A14C-480DEC363ABC,1097AEA2-1870-4C0C-B360-0AD57A307C0F,12C0DD0A-CB2D-45B2-BC4B-793257F1F18F,AB674966-275E-49F8-A388-352C24BE3911,B126F4C3-61CD-4C53-ACDA-F78A7E90F3AA,54BEAFBC-3F32-41ED-AC76-9566857CCC7A,6ACB8C93-FDA5-447B-A200-75B510F43BB9,B83A7B11-7CB6-4FAB-850B-47E4E0E1CE3D,508B7B1E-39D7-42DA-A442-74BF21CEFE32,4A4FEB81-9097-46D1-9C23-2D3B0A145AE6,ABEA5B7C-63F2-494E-AD2B-EC4F74AB59C5,A7B20E81-036C-476F-A589-35C6D1C24907,7BE08E80-4C0B-49FD-9378-BE4174CD68AF,2B9F4EBD-FC04-4783-B744-75FDEB21D37B,0B51708A-ECAE-4733-8722-7DC1565C3AA3,5B21E4A5-4D2C-482A-9455-6181B1A34309,013FA80B-1DED-4CC1-A429-C86FFEF02985,3C6F001D-9197-4787-BEC4-1E1C9FE99C2B,ABA23765-C445-46DA-8208-BF6F97E9B9C4,CF50E0E5-85AC-4AE6-9474-01E7D2B60A08,AA305AF6-17EE-4388-B106-3F882ED17A28,DEBBC7E9-7301-45A9-B9E6-B6F00B2C86ED,EF486A74-31F5-4759-A9D4-B99785E8ACD1,DED99940-FCF7-41E0-9849-6A1AA540CD74,65EDDC64-3A5C-4809-BD06-4358BA051069,4C6DFDC1-2CEA-4D08-AEDB-C41EAE415EA8,BBB0370B-9563-421B-BBC9-42F231197DBC,3D759F0D-D981-4248-B5DF-CDE0810DBA6E,0DE9298E-CA79-4277-9547-9D506508880C,05238619-F986-4728-A006-0F57E