How to Split a Delimited String in SQL Server: Accessing Individual Items Easily

When working with strings in SQL Server, you might encounter situations where you need to access specific items within a delimited string. For example, you may have a string like “Hello John Smith” and want to extract “John” from it. In this blog post, we’ll explore how to efficiently split strings in SQL Server so you can access individual components without hassle.

Problem Overview

You have a string:

"Hello John Smith"

You want to split it by spaces and obtain the second item (index 1), which is “John.” This can be tricky when working with SQL Server, but fortunately, there are ways to accomplish this task effectively.

Solution: Using SQL Server’s String Functions

SQL User Defined Function

To handle the splitting of delimited strings, a SQL User Defined Function (UDF) can be quite beneficial. There’s a detailed guide on how to create a UDF for parsing a string, which you can find here. This UDF will allow you to easily access individual items after splitting.

Implementing the Logic

Here is a simple example that demonstrates how to split a delimited string using T-SQL scripts. We’ll work with another example string:

'1|20|3|343|44|6|8765'

The following SQL code snippet outlines how to achieve string splitting:

DECLARE @products VARCHAR(200) = '1|20|3|343|44|6|8765'
DECLARE @individual VARCHAR(20) = NULL

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

Explanation of the Code

• Declare Variables: @products holds the original string, while @individual is initialized as NULL to store the split components.
• WHILE Loop: This loop continues as long as there are remaining characters in @products.
• PATINDEX Function: It finds the position of the delimiter (’|’). If found, the string is split.
• SUBSTRING Function: Retrieves the individual components between the delimiters and selects them one by one.
• Output the Results: Each split item is output sequentially.

Benefits of This Approach

• Customizability: You can modify the script to work with different delimiters or string formats.
• Performance: Efficiently splits strings without creating external dependencies.
• Simplicity: Easy to understand and implement even for beginners in SQL Server.

Conclusion

By following the steps above, you can effectively split delimited strings in SQL Server, allowing you to access individual components. Whether you’re dealing with simple strings or more complex data sets, understanding how to manipulate strings is essential for enhancing your SQL skills.

For more resources on SQL Server techniques, feel free to explore additional tutorials and guides. Happy coding!