[Sqlserver] database custom split string function split ())

[Sqlserver] database custom split string function split ()

We often use string splitting when using batch operations. The depressing thing is that SQL server does not have its own split function, so we can only solve it by ourselves. In order to reduce the number of communications with the database, we will use this method for batch operations. Of course, sometimes we use the execute method to implement it. The bad thing with this method is that she only knows strings separated, the batch operation of IDS can still be achieved, but we often need to do more complex operations by ourselves ......

1. When we need to input a long string, we can use the ntext and text types. The difference between them is that Unicode and ANSI character sets are supported. It should be noted that when we want to calculate the string length, we need to use datalength () instead of Len (). In the ntext type, one character occupies two bytes, so when calculating the character, don't forget to divide it by 2. Let's take a look at the example to illustrate everything.

-- ===================================================== ======
-- Author: <myxbing>
-- Create Date: <2007/8/17>
-- Description: <split string function>
-- ===================================================== ======
Create Function [DBO]. [split]
(
@ Splitstring text, -- if you want to input the ntext type, you need to modify it as follows. The annotation behavior is the same as ntext.
@ 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 need to split a long string that may exceed the length of (n) varchar. Of course, to be compatible with SQL Server2000, max cannot be used. Therefore, we need to use (n) to split the string) text. Note that the (n) text type cannot be defined in local variables. However, we can add the strings from substring to table variables directly, instead of insert.

2. When we pass in (n) varchar, we can use Len to calculate the length. It is worth noting that the maximum length of nvarchar is 4000, and the maximum length of varchar is 8000. Next let's take a look at the code, which is basically no different from the above Code.

-- ===================================================== ======
-- Author: <myxbing>
-- Create Date: <2007/8/18>
-- Description: <split string function>
-- ===================================================== ======
Create Function [DBO]. [ufn_split]
(
Content varchar (8000 ),
@ Seperator varchar (20)
)
Returns @ temp table
(
-- Id int identity (1, 1 ),
Value varchar (8000)
)
Begin
Declare @ currindex int, @ nextindex int
Select @ currindex = 1
Select @ nextindex = 1
Select @ nextindex = charindex (@ seperator, @ content)
While (@ nextindex> 0)
Begin
If (@ nextindex >=@ currindex + 1)
Begin
Insert into @ temp (value) values (substring (@ content, @ currindex, @ nextindex-@ currindex ))
End
Select @ currindex = @ nextindex + 1
Select @ nextindex = charindex (@ seperator, @ content, @ currindex)
End
If @ currindex <= datalength (@ content)
Insert into @ temp (value) values (substring (@ content, @ currindex, datalength (@ content)-@ currindex + 1 ))
Return
End

3. Split the string to get int-type data. This is relatively simple. The code is similar to the above and will not be given here. You can rewrite it based on the above Code.

Because the database does not contain arrays, only table variables can be returned. Therefore, you must define Table value functions when defining these functions. With these functions, we can use them to perform more effective batch operations. Of course, because the variables are set with overflow in mind, it may affect the performance, but compilation may also bring us a lot of results. Please use them with caution.