STRING_SPLIT Function
SQL Server introduced the STRING_SPLIT function to directly split delimited strings into a table of values. It takes the input string and delimiter as parameters, returning a table with individual items. This method is efficient and straightforward, ideal for modern SQL Server environments.
Example: Splitting Delimited Strings into Individual Values in SQL
-- Create a sample table with a column containing delimited strings
CREATE TABLE SampleData (
ID INT,
Data VARCHAR(100)
);
-- Insert some sample data
INSERT INTO SampleData (ID, Data)
VALUES
(1, 'apple,banana,orange'),
(2, 'carrot,potato,tomato');
-- Use SUBSTRING_INDEX to split the delimited strings into individual values
SELECT ID,
SUBSTRING_INDEX(SUBSTRING_INDEX(Data, ',', n.n), ',', -1) AS SplitData
FROM SampleData
JOIN (
SELECT 1 n UNION ALL
SELECT 2 UNION ALL
SELECT 3 -- Add more if needed based on maximum elements in the list
) n ON LENGTH(Data) - LENGTH(REPLACE(Data, ',', '')) >= n.n - 1
ORDER BY ID, SplitData;
Output:
Using “STRING_SPLIT”
- Uses the STRING_SPLIT function to split the delimited strings into individual values.
- Orders the output by ID and SplitData for clarity.
- Each row includes the original ID value and the corresponding split data (SplitData).
- The output is sorted by ID and SplitData for easier interpretation.
- Demonstrates how STRING_SPLIT converts delimited strings into individual values, facilitating data processing and analysis.
How to Split a Delimited String to Access Individual Items in SQL?
In SQL, dealing with delimited strings is a common task, especially when handling data that are not structured in a traditional tabular format. Whether it’s parsing a list of values separated by commas or any other delimiter, splitting these strings into individual items is crucial for various data manipulation tasks.
In SQL, sometimes we get data that’s all squished together, like a bunch of words separated by commas or other symbols. This article is all about learning how to do just that—take a long string of text and break it into pieces we can easily work with.
Contact Us