How to Split a Delimited String to Access Individual Items in SQL?

When working with strings, we may need to separate them to access the data easier, especially when the data is stored in a single column. In such cases, a delimiter will be used to split them into multiple strings. In this blog, you will learn all the approaches for splitting a delimited string in SQL and efficiently accessing individual items, with detailed explanations with examples for each.

Table of Contents:

• Methods to Split a Delimited String in SQL and Access Individual Items
  • Method 1: Using SUBSTRING_INDEX() Function in SQL
  • Method 2: Using STRING_SPLIT
  • Method 3: Using XML in SQL
  • Method 4: Using a Recursive CTE in SQL
  • Method 5: Using a Table-Valued Function in SQL
• Performance Considerations
• Alternative Approach: Using JSON
• Real-World Examples
• Conclusion

Let’s create an example to perform the splitting of delimited strings to access them as individual items.

Example:

-- Creating the table Books
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    Genres VARCHAR(255) -- Stored as a comma-separated string
);

-- Inserting data into the Books table
INSERT INTO Books (BookID, Title, Genres) VALUES
(1, 'The Women In White', 'Mystery,Thriller'),
(2, 'Last Day On Mars', 'Science,Space'),
(3, 'Wings of fire', 'Philosophy, Self-Help');
select * from Books;

Output:

This is how the table looks after creating and inserting some values into it.  

Methods to Split a Delimited String in SQL and Access Individual Items

There are methods to split the delimited string in SQL, like substring_index(), string_split, XML, and recursive CTE:

Method 1: Using SUBSTRING_INDEX() Function in SQL

The SUBSTRING_INDEX() function in SQL is useful when you want to extract specific parts of a string based on a delimiter. The SUBSTRING_INDEX() is efficient when working on a MySQL server. 

Example:

SELECT 
    BookID, 
    Title,
    SUBSTRING_INDEX(Genres, ',', 1) AS Genre1,
    SUBSTRING_INDEX(Genres, ',', -1) AS Genre2
FROM Books;

Output:

Explanation: The SUBSTRING_INDEX() will not find the string and separate it, this will separate based on the delimiter position. Here, the genre has been separated into two columns instead of one.

Method 2: Using STRING_SPLIT

The string_split method in SQL Server automatically converts delimited strings into multiple rows. The String_split uses delimiter as ( , , ;, |), etc. 

Example:

SELECT BookID, value AS Genre
FROM Books
CROSS APPLY STRING_SPLIT(Genres, ',');

Output:

Explanation: The String_split method is a simple method to convert delimited strings into multiple rows. By converting it into multiple rows, we can easily access each item separately. 

Method 3: Using XML in SQL

XML is an alternative for string_split. If you are using an older version of SQL, then you can use XML. The XML will fetch the desired output.

Example:

SET QUOTED_IDENTIFIER ON;
SELECT BookID,
       x.value('.', 'VARCHAR(100)') AS Title
FROM (
    SELECT BookID, CAST('<x>' + REPLACE(Title, ',', '</x><x>') + '</x>' AS XML) AS TitleXML
    FROM Books
) t
CROSS APPLY TitleXML.nodes('/x') AS xTable(x);

Output:

Explanation: The XML method fetched the desired string titles of the books from the records. Now, we can access those items easily. 

Method 4: Using a Recursive CTE in SQL 

The Recursive CTE in SQL helps to split the delimited string into two sets of data. The recursive CTE will first return the first value in the data, then if the remaining genre is empty, the last value might be extracted again. 

Example:

WITH GenreCTE AS (

    -- Anchor Query: Get the first genre
    SELECT 
        BookID, 
        LEFT(Title, 20) AS ShortTitle,  -- Truncate Title to 20 characters
        LEFT(LEFT(Genres, CHARINDEX(',', Genres + ',') - 1), 15) AS ShortGenre, -- Truncate Genre to 15 characters
        STUFF(Genres, 1, CHARINDEX(',', Genres + ','), '') AS RemainingGenres
    FROM Books
    WHERE Genres IS NOT NULL
    UNION ALL

    -- Recursive Query: Extract the next genre
    SELECT 
        BookID, 
        ShortTitle, 
        LEFT(LEFT(RemainingGenres, CHARINDEX(',', RemainingGenres + ',') - 1), 15),
        STUFF(RemainingGenres, 1, CHARINDEX(',', RemainingGenres + ','), '')
    FROM GenreCTE
    WHERE RemainingGenres <> ''
)
SELECT BookID, ShortTitle AS Title, ShortGenre AS Genre FROM GenreCTE;

Output:

Explanation: FROM GenreCTE WHERE RemainingGenres <>, this function helps to fetch the remaining data from the delimited strings. If the remaining genre is empty, the substring will not fetch those rows. 

Method 5: Using a Table-Valued Function in SQL

The table-valued function helps to convert delimited strings into separated rows instead of concatenated strings. This is easy to use and easily reusable.  

Example:

GO
CREATE FUNCTION dbo.SplitString (@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS TABLE
AS
RETURN (
    SELECT TRIM(value) AS Value FROM STRING_SPLIT(@String, @Delimiter)
);
GO
SELECT BookID, value AS Genre
FROM Books
CROSS APPLY dbo.SplitString(Genres, ',');

Output:

Explanation: The table-valued function in SQL, using the command SELECT TRIM(value) AS Value FROM STRING_SPLIT(@String, @Delimiter) to get the delimited string into separate values in a column. 

Performance Considerations

Alternative Approach: Using JSON

Using the JSON function is an efficient way to split delimited strings in SQL Server. JSON parsing is fast and flexible. 

Example:

SELECT 
    BookID, 
    Genre

FROM Books
CROSS APPLY OPENJSON('["' + REPLACE(Genres, ',', '","') + '"]')
WITH (Genre VARCHAR(100) '$');

Output:

Explanation: The JSON parsing method in SQL converts delimited strings into rows, and then OPENJSON will extract the data. 

Real-World Examples

Case 1: Travel booking: Flight booking of cities through flight layover. 

Example:

CREATE TABLE FlightBookings (
    BookingID INT PRIMARY KEY,
    PassengerName VARCHAR(255),
    LayoverCities VARCHAR(255) -- Stored as a comma-separated string
);

INSERT INTO FlightBookings (BookingID, PassengerName, LayoverCities) VALUES
(1, 'Alice Johnson', 'London,Dubai,Singapore'),
(2, 'Mark Smith', 'Paris,Frankfurt,Bangkok'),
(3, 'Emma Davis', 'New York,Amsterdam,Tokyo');

SELECT BookingID, 
       LEFT(PassengerName, 10) AS ShortName, 
       LEFT(value, 10) AS ShortLayoverCity
FROM FlightBookings
CROSS APPLY STRING_SPLIT(LayoverCities, ',');

Output:

Explanation: The cross-apply fetched all the details of the flight and their layover city. 

Case 2: Social media platform with plenty of hashtags. We will separate the hashtags by columns.

Example:

-- Create table
CREATE TABLE SocialMediaPosts (
    PostID INT PRIMARY KEY,
    UserName VARCHAR(255),

  -- Stored as a comma-separated string
  Hashtags VARCHAR(255) 
);

-- Insert sample data
INSERT INTO SocialMediaPosts (PostID, UserName, Hashtags) VALUES
(1, 'travel_lover', '#Travel,#Food,#Photography'),
(2, 'fitness_guru', '#Workout,#Health,#Motivation'),
(3, 'tech_enthusiast', '#AI,#Tech,#Innovation');

-- Query to split hashtags while limiting output display size
SELECT 
    PostID, 
    LEFT(UserName, 15) AS ShortUserName,  
    LEFT(value, 12) AS ShortHashtag   
FROM SocialMediaPosts
CROSS APPLY STRING_SPLIT(Hashtags, ',');

Output:

Explanation: The CROSS APPLY STRING_SPLIT(Hashtags, ‘,’) converted the CSV into columns. 

Conclusion

To split the delimited string in SQL, we discussed various methods to access individual items efficiently. Methods like SUBSTRING_INDEX(), STRING_SPLIT, XML, CTE, and TVF (Table-valued functions) will be very useful in converting delimited strings to rows. The alternative method, like JSON, is also efficient and flexible. To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

How to Split a Delimited String in SQL and Access Individual Items – FAQs