Splitting strings is a common task in SQL Server, especially when dealing with delimited values stored in a single column. In this blog post, we will explore different approaches to splitting strings and finding the count of values.

Let’s start by setting up the environment for testing. We will create a table called “SplitWithCount” with a column named “cols” of type VARCHAR(100). We will insert some sample values into this table:

USE tempdb
GO

-- Create a table
CREATE TABLE SplitWithCount
(
    cols VARCHAR(100)
)
GO 

-- Insert Some Value
INSERT INTO SplitWithCount (cols)
VALUES ('Pinal;Dave;Kumar'),('Pinal;Dave'),('Pinal')
GO

Our goal is to split the values in the “cols” column and find out the count of each value. Here is the desired output:

SplitMyString | Counts
----------------------
Pinal         | 3
Dave          | 2
Kumar         | 1

If you are using SQL Server 2016 or later, you can achieve this easily using the STRING_SPLIT function:

SELECT ss.value AS SplitMyString, COUNT(ss.value) AS Counts 
FROM SplitWithCount
CROSS APPLY STRING_SPLIT(cols, ';') ss
GROUP BY ss.value

This solution is elegant and efficient, but what if you are using an earlier version of SQL Server? In that case, you can use a slightly more complex approach using XML:

SELECT SplitMyString, COUNT(*) AS Counts 
FROM
(
    SELECT CAST(CONCAT('', REPLACE(cols, ';', ''), '') AS XML) Xmlcol 
    FROM SplitWithCount
) s
CROSS APPLY
(
    SELECT ProjectData.D.value('.', 'VARCHAR(10)') AS SplitMyString
    FROM s.xmlcol.nodes('Cols') AS ProjectData(D)
) a
GROUP BY a.SplitMyString

These are just two examples of how you can split strings in SQL Server. There might be other alternate solutions as well. If you are aware of any, please leave a comment and I will be happy to publish it on the blog with due credit.

Splitting strings can be a challenging task, but with the right approach, it can be accomplished efficiently in SQL Server. Whether you are using the STRING_SPLIT function in SQL Server 2016 or the XML approach in earlier versions, understanding how to split strings is a valuable skill for any SQL Server developer.