SQL Server implements custom split String function split ()

Often we want to use the bulk operation will be used to split the string, the sad is that SQL Server does not have a split function, so we can only do it ourselves to solve. In order to reduce the number of communications with the database, we will use this method to achieve bulk operations. Of course, sometimes we use the Execute method to implement this, one of the bad things about using this method is that she only recognizes strings that are split by "," and can achieve the goal when transmitting IDs bulk operations, but often we need to do it ourselves when we use more complex operations. ......

1. When we need to pass in a very long string we can use the ntext and text types, and their differences are one that supports Unicode and one that supports the ANSI character set. Note that when we want to compute the string length we need to use DATALENGTH () instead of Len (), a character in the ntext type occupies two bytes, so don't forget to divide the characters by 2, so let's take a look at the example below to illustrate everything.

-- =============================================
--Author: <myxbing>
--Create Date: <2007/8/17>
--Description: < split string function >
-- =============================================
CREATE FUNCTION [dbo]. [Split]
(
@SplitString text--If you want to pass in the ntext type, the following needs to be modified, and the comment behavior ntext the same
@Separator varchar (2) = ', '--NVarChar (2) = N ', '
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity (1,1),
[value] varchar (8000)--NVarChar (4000)
)
As
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText varchar (8000);--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

Sometimes we split it up or it takes a long string to be able to exceed the length of the (N) varchar, of course, in order to be compatible with SQL Server2000 can not use Max, so we split the string or to use (n) text to express, we need to note that in the local variable can not be defined (n) The type of text, but we can add the substring out of the string directly into the table variable instead of paying the value after the insert.