In SQL Server 2022, the STRING_SPLIT function has been enhanced, making it a powerful tool for parsing and handling delimited strings. This blog will provide an exhaustive overview of these enhancements, using the JBDB database for demonstrations. We’ll explore a detailed business use case, delve into the new features, and provide T-SQL queries for you to practice and master the updated STRING_SPLIT function. Let’s dive in! 🌊
Business Use Case: Customer Preferences Analysis 🛍️
Imagine you’re working for an e-commerce company that tracks customer preferences for various product categories. Each customer’s preference is stored as a comma-separated string in the database. Your task is to analyze these preferences to offer personalized recommendations and optimize the marketing strategy.
For instance, the data might look like this:
- Customer 1:
Electronics,Books,Toys - Customer 2:
Groceries,Fashion,Electronics - Customer 3:
Books,Beauty,Fashion
With the enhancements in STRING_SPLIT in SQL Server 2022, you can efficiently parse these strings and analyze the data. Let’s explore how!
STRING_SPLIT Enhancements in SQL Server 2022 🚀
In SQL Server 2022, STRING_SPLIT has been enhanced to include:
- Ordinal Output: A new parameter,
ordinal, can now be specified to include the position of each substring in the original string. - Improved Performance: Enhanced indexing capabilities for better performance in large datasets.
Syntax:
STRING_SPLIT ( string, separator [, enable_ordinal ] )string: The input string to be split.separator: The delimiter character.enable_ordinal: Optional; specifies whether to include the ordinal position of each substring (0 or 1).
Example 1: Basic Usage 🌟
Let’s start with a simple example to see the new ordinal feature in action.
Setup:
USE JBDB;
GO
CREATE TABLE CustomerPreferences (
CustomerID INT PRIMARY KEY,
Preferences VARCHAR(100)
);
INSERT INTO CustomerPreferences (CustomerID, Preferences)
VALUES
(1, 'Electronics,Books,Toys'),
(2, 'Groceries,Fashion,Electronics'),
(3, 'Books,Beauty,Fashion');
GOQuery with STRING_SPLIT:
SELECT CustomerID, value, ordinal
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1);
This output shows the customer preferences along with their order of appearance. The ordinal column is a new addition in SQL Server 2022, providing valuable information about the sequence of items.
Example 2: Analyzing Preferences 🔍
Now, let’s say we want to find out the most popular categories among all customers.
Query to Find Most Popular Categories:
SELECT value AS Category, COUNT(*) AS Count
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
GROUP BY value
ORDER BY Count DESC;
From the output, we can see that ‘Electronics’, ‘Books’, and ‘Fashion’ are the most popular categories. This data can be used to tailor marketing campaigns and inventory management.
Extracting Categories Based on Position:
- Find customers whose second preference is ‘Fashion’:
SELECT CustomerID
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
WHERE ordinal = 2 AND value = 'Fashion';Counting Unique Categories:
- Count the number of unique categories preferred by customers:
SELECT COUNT(DISTINCT value) AS UniqueCategories
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1);Combining STRING_SPLIT with Other Functions:
- Find the length of each preference category string:
SELECT CustomerID, value, LEN(value) AS Length
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1);Analyzing Preferences by Customer:
- Count the number of preferences each customer has:
SELECT CustomerID, COUNT(*) AS PreferenceCount
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
GROUP BY CustomerID;Extracting Values by Ordinal Position:
- Identify customers whose first preference is ‘Electronics’:
SELECT CustomerID
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
WHERE ordinal = 1 AND value = 'Electronics';
Finding Specific Ordinal Positions:
- Retrieve all customers whose third preference includes ‘Books’:
SELECT CustomerID
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
WHERE ordinal = 3 AND value = 'Books';Filtering Based on Multiple Conditions:
- Find customers who have ‘Books’ in any position and ‘Fashion’ as the last preference:
SELECT CustomerID
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
GROUP BY CustomerID
HAVING SUM(CASE WHEN value = 'Books' THEN 1 ELSE 0 END) > 0
AND MAX(CASE WHEN value = 'Fashion' THEN ordinal ELSE 0 END) = COUNT(*);
Analyzing Distribution of Preferences:
- Determine the number of customers who have each category as their first preference:
SELECT value AS FirstPreference, COUNT(*) AS Count
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
WHERE ordinal = 1
GROUP BY value
ORDER BY Count DESC;
Combining STRING_SPLIT with String Functions:
- Find the customers with the longest category name in their preferences:
SELECT CustomerID, value, LEN(value) AS Length
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
ORDER BY Length DESC;
Using STRING_SPLIT for Data Transformation:
- Convert customer preferences into a single concatenated string with a different delimiter:
SELECT CustomerID, STRING_AGG(value, '|') AS ConcatenatedPreferences
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
GROUP BY CustomerID;
Analyzing Preference Patterns:
- Find the most common pattern of the first two preferences:
WITH FirstTwoPreferences AS (
SELECT CustomerID, STRING_AGG(value, ',') WITHIN GROUP (ORDER BY ordinal) AS Pattern
FROM CustomerPreferences
CROSS APPLY STRING_SPLIT(Preferences, ',', 1)
WHERE ordinal <= 2
GROUP BY CustomerID
)
SELECT Pattern, COUNT(*) AS Count
FROM FirstTwoPreferences
GROUP BY Pattern
ORDER BY Count DESC;
Conclusion 🏁
The enhancements in SQL Server 2022’s STRING_SPLIT function, particularly the introduction of the ordinal parameter, provide powerful tools for handling and analyzing delimited strings. Whether you’re working with customer data, logs, or any form of delimited information, these enhancements can streamline your processes and deliver valuable insights.
Happy querying! 😄
For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.