mssql-string separation (Split function)

The previous mention of record consolidation, there must be a merger requirement there will be separate requirements, when it comes to the separation of strings, you will certainly think of the split function, which in. Net,java and JS have functions, unfortunately in SQL Server, we can only write such a function.

First of all to analyze how to write this function, in the case of the previously merged string, "Zhang San, John Doe, Harry, Qian Six, Zhao Seven, Tom,amy,joe,leo", now to separate this string according to "," to get a set of data, it can now be concluded that the function passed in the variable has two, one is the target string , we define as @string, the data type is nvarchar (500), the nvarchar () is chosen to take into account all Unicode character sets, such as Chinese, and the other variable is a delimiter, which we define as @separator, with a data type of nvarchar (10 ), the output variable is defined as @array, a table with a single column named string and a data type of nvarchar (500).

Below to analyze the specific split method, with the string "Zhang San, John Doe, Harry, Qian Six, Zhao Seven, Tom,amy,joe,leo" as an example, first to find the first "," and then the "Zhang San" intercept it, and then the target string into "John Doe, Harry, Money six, Zhao Seven, Tom,amy,joe , Leo ", and then find the first", "," John Doe "intercepted, and so on, until the", "and so on, with this idea, the following direct implementation, find the string in the program language generally used IndexOf method, intercept the general use of substring method, SQL Server has the SUBSTRING function to intercept the string, but there is no indexof lookup function, instead of the charindex and PATINDEX functions, charindex for the exact match lookup, and patindex for fuzzy matching lookup, According to our requirements, choose 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 ($), @tagString nvarchar (500)

--ADD the T-SQL statements to compute the return value here

SET @[email protected]

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) [email protected])

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 it:

SELECT * FROM dbo. SPLIT (N ' Zhang San, John Doe, Harry, Qian Six, Zhao Seven, Tom,amy,joe,leo ', ', ')

The following results can be obtained:

This article transferred from: http://www.cnblogs.com/leolis/p/3977569.html

mssql-string separation (Split function)