MSSQL-string separation (Split function), mssqlsplit

MSSQL-string separation (Split function), mssqlsplit

As mentioned above, record merging will certainly require separation when combined. When it comes to string separation, you will surely think of the SPLIT function. NET, Java, and JS all have functions. Unfortunately, they are not in SQL SERVER. We can only write such a function by ourselves.

First, let's analyze how to write this function. Taking the previously merged string as an example, "Zhang San, Li Si, Wang Wu, Qian Liu, Zhao Qi, Tom, Amy, Joe, Leo ", now we want to separate this string by "," to get a set of data. Now we can see that there are two input variables for this function. One is the target string, which is defined as @ string, the data type is nvarchar (500), and nvarchar () is selected to take into account all Unicode character sets, such as Chinese. The other variable is a separator, which is defined as @ separator, the data type is nvarchar (10), the output variable is defined as @ array, a single column name is String, and the data type is nvarchar (500) Table.

Next, we will analyze the specific sharding method. Taking the string "Zhang San, Li Si, Wang Wu, Qian Liu, Zhao Qi, Tom, Amy, Joe, Leo" as an example, we first need to find the first ", ", then truncate" Zhang San "and change the target string to" Li Si, Wang Wu, Qian Liu, Zhao Qi, Tom, Amy, Joe, Leo ", then, find the first ",", cut out "Li Si", and so on until "," is not found. With this idea, we will implement it directly below, the indexOf method is generally used to search for strings in programming languages. The Substring method is used to intercept strings. The SUBSTRING function in SQL SERVER is used to intercept strings, but there is no indexOf lookup function. Instead, the CHARINDEX and PATINDEX functions are used, CHARINDEX is used for exact match search, while PATINDEX is used for fuzzy match search. Based on our needs, select the CHARINDEX function. The entire SPLIT Function Code is as follows:

CREATE FUNCTION [dbo].[SPLIT] 
(
-- Add the parameters for the function here
@string nvarchar(500),  
    @separator nvarchar(10) 
)
RETURNS @array TABLE(String nvarchar(500)) 
AS
BEGIN
-- Declare the return variable here
DECLARE @separatorIndex int,@tempString nvarchar(500),@tagString nvarchar(500)
-- Add the T-SQL statements to compute the return value here
    SET @tagString=@string
SET @separatorIndex=CHARINDEX(@separator,@tagString)  
    WHILE(@separatorIndex<>0)
BEGIN
        SET @tempString = SUBSTRING(@tagString,1,@separatorIndex-1)  
        INSERT INTO @array(String) VALUES(@tempString)  
        SET @tagString = SUBSTRING(@tagString,@separatorIndex+1,LEN(@tagString)-@separatorIndex)  
        SET @separatorIndex=CHARINDEX(@separator,@tagString)  
    END  
    SET @tempString = @tagString  
    IF (LEN(@tempString)>0)  
        INSERT INTO @array(String) VALUES(@tagString)  
-- Return the result of the function
RETURN
END
GO

Then test:

SELECT * FROM dbo. SPLIT (N 'zhang San, Li Si, Wang Wu, Qian Liu, Zhao Qi, Tom, Amy, Joe, Leo ',',')

The following result is displayed:

 

This article