------------------------------------------------------------------------Copyright: 2011--Time: 2011-06-17--use: Slicing strings, Null characters between consecutive separators and delimiters are ignored--usage: SELECT * FROM [dbo]. [Wf_split] (' A,b,c,d ', ', ');-- returns the table structure:-- id the self-increment primary key-- rs Each paragraph of the delimiter slice----------------------------------------------------------------------use qptreasuredbgoif EXISTS ( SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Wf_split] ') and type in (n ' FN ', n ' IF ', n ' TF ', n ' FS ', n ' FT ')) DROP FUNCTION dbo. [Wf_split] GO----------------------------------------------------------------CREATE FUNCTION [dbo]. [Wf_split] ( @strSource NVARCHAR (4000), --string to manipulate @strSeparator CHAR (1) --delimiter, single byte) RETURNS @tbResult TABLE (id INT IDENTITY (), RS NVARCHAR (+)) with encryption Asbegindeclare @ Dwindex INT, @strResult NVARCHAR (1000); SET @strSource = RTRIM (LTRIM (@strSource)); --Blanking set @dwINdex = CHARINDEX (@strSeparator, @strSource); --gets the position of the first delimiter
While @dwIndex >0 BEGIN SET @strResult = LTRIM (RTRIM (@strSource, @dwIndex-1)); IF @strResult is NULL or @strResult = "or @strResult = @strSeparator BEGIN SET @strSeparator = SUBSTRING (@strSource, @dwI Ndex+1,len (@strSource) [email protected]); --the string to be manipulated removes the segmented part SET @dwIndex = CHARINDEX (@strSeparator, @strSource); --cyclic volume increases CONTINUE; END
INSERT @tbResult VALUES (@strResult); SET @strSource = SUBSTRING (@strSource, @dwIndex +1,len (@strSource)-[email protected]); --the string to be manipulated removes the segmented part SET @dwIndex =charindex (@strSeparator, @strSource); --loop increment end-handles the last section IF @strSource is not NULL and LTRIM (RTRIM (@strSource)) <> "and @strSource <> @strSepara Tor BEGIN INSERT @tbResult VALUES (@strSource) END
RETURN; END
SQL Server Split function
