How Can I Split Delimited Strings in SQL Server Without a Native Split Function?

Divide the separatist string in SQL Server

SQL Server lacks the native string segmentation function, which brings challenges when processing the separator string. However, we can cleverly use the PARSENAME function to solve this problem.

It is necessary to divide the string like "Hello John Smith" and access a specific index at a specific index at a specific index according to the segments (such as space), please follow the steps below:

Use different characters to replace the separator:

Use the replace () function to replace the space to unique character, such as an end. For example:

Use the PARSENAME split string:

<code class="language-sql">REPLACE('Hello John Smith', ' ', '.')</code>

PARSEENAME function is divided into modified string according to the new separator. Its second parameter determines which paragraph to retrieve. To visit the project 1 (John), please use:

2. This method is achieved by creating a temporary table with only one column, and each value represents a fragment of the original string. However, it should be noted that if the original string already contains a sentence character, this method may not be applicable.

Third -party solution

<code class="language-sql">SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)</code>

Or, you can consider using a user -defined function (UDF) specially used for string segmentation. This method provides greater flexibility and reliability.

The following is a UDF example using a specified separators segment strings:

How to use example:

To use the separators "" "division string" Hello John Smith ", please execute the following inquiries:

<code class="language-sql">CREATE FUNCTION Split(@String VARCHAR(MAX), @Delimiter VARCHAR(1))
RETURNS TABLE
AS
RETURN (SELECT Item FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY r.rn) AS rn,
           LEFT(s, CHARINDEX(@Delimiter, s) - 1) AS Item
    FROM (SELECT @String AS s, 1 AS rn
          UNION ALL
          SELECT SUBSTRING(@String, CHARINDEX(@Delimiter, @String) + 1, LEN(@String)), rn + 1
          FROM Split(@String, @Delimiter)) AS r
    WHERE s IS NOT NULL
) AS t)</code>

This query will return a table containing two columns: RN (index of the project) and Item (the value of the project).

The above is the detailed content of How Can I Split Delimited Strings in SQL Server Without a Native Split Function?. For more information, please follow other related articles on the PHP Chinese website!