Diy SQL string decomposition function Split

Some time ago, we encountered this problem when performing a batch data review or deletion function:

Because the review or deletion operation is performed in the stored procedure, we plan to splice the primary key of the selected data into a string and upload it to the stored procedure for separation before processing.

In C # and JavaScript, there is a Split function that separates strings. It is assumed that there are similar functions in SQL. The results are not found in the MSSQL help document for half a day. No way, I had to write it myself. I searched the relevant articles on the Internet and read one or two articles about the User-Defined Functions of SQL separator strings. The result was a bit disappointing. It may be my own poor level, or because someone else's code didn't write comments. In short, it was very difficult to look at it. Before reading it, I decided to write it myself...

The idea is simple: in the string to be decomposed, if a specified separator exists, the string before the first separator is taken out and saved to the table, delete the extracted string and the first Separator in the string to be decomposed, and then continue the next decomposition. If the specified separator still exists, it will be decomposed)

The following are SQL statements:

 
 

  
  
-- ===================================================== ======
  
  
-- Author: Henson
  
  
-- Create date: 2011-04-20
  
  
-- Description: String separation function
  
  
-- ===================================================== ======
  
  
Alter function [dbo]. [Split]
  
  
(
  
  
@ StrText varchar (3000), -- the original string to be separated
  
  
@ StrSplit varchar (100) -- delimiter
  
  
)
  
  
RETURNS @ temp Table
  
  
(
  
  
ID int identity primary key,
  
  
SingleVal varchar (1000)
  
  
)
  
  
AS
  
  
BEGIN
  
  
Declare @ intLen int -- used to store the length of the original string to be detached
  
  
Declare @ intSplitLen int -- delimiter Length
  
  
Declare @ intIndex int -- used to store the position of the separator string in the original string
  
  
Declare @ strVal varchar (1000) -- used to store the separated string
  
  
-- Get the length of the original string
  
  
Set @ intLen = LEN (RTRIM (LTRIM (@ strText )))
  
  
Set @ intSplitLen = LEN (RTRIM (LTRIM (@ strSplit )))
  
  
-- The original string is not empty before further separation.
  
  
If (@ intLen> 0)
  
  
Begin
  
  
-- Loop the original string until the original string is separated
  
  
While CHARINDEX (@ strSplit, @ strText)> 0
  
  
Begin
  
  
-- Obtain the position of the separator string in the original string
  
  
Set @ intIndex = CHARINDEX (@ strSplit, @ strText)
  
  
-- Obtain the separated string and insert it into the table
  
  
Set @ strVal = RTRIM (LTRIM (LEFT (@ strText, @ intIndex-1 )))
  
  
If (LEN (@ strVal)> 0)
  
  
Begin
  
  
Insert Into @ temp (SingleVal) values (@ strVal)
  
  
End
  
  
-- Remove the separated string from the original string, including the separator.
  
  
Set @ strText = Substring (@ strText, @ intIndex + @ intSplitLen, @ intLen-@ intIndex)
  
  
-- Reset the length of the original string
  
  
Set @ intLen = LEN (@ strText)
  
  
End
  
  
-- If the original string after separation is still not empty, the table should also be inserted.
  
  
If (LEN (RTRIM (LTRIM (@ strText)> 0)
  
  
Begin
  
  
Insert Into @ temp (SingleVal) values (@ strText)
  
  
End
  
  
End
  
  
Return
  
  
END
 
 

The delimiter can be multiple characters)

This is generally the case when multiple strings are organized: strKeys + = strSingleKey + ",";

Therefore, the string may end with a comma. If the end of the string is removed after the organization is complete, it will not end with a comma. Therefore, after the loop decomposition is complete, if left and right spaces are removed from the remaining original string), if there is still content, it should also be stored in the table.

Maybe I am not the best in writing this kind of code, and there may be problems. If you really need to improve it, please point it out. Thank you very much !!!