Some puzzles come as legacy and a great way to do sometimes interviews. In the past, I have written some innovative solutions when it comes to splitting strings inside SQL Server. One of them is at: SQL SERVER – Split Comma Separated List Without Using a Function. Though this is a good solution, many have some back to say this is not a clean or a native way to work with strings. Let us learn about Splitting Strings puzzle.
Let me complicate this statement a bit and set the stage for you. Assume you have a column that has a delimited value inside it. Our requirement is to split the values and find out the count of values. So I am making the split string conversation into an aggregation too. Nothing fancy about this though.
Splitting Strings Puzzle
Let us set up the environment for testing.
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 -- Retrieve data SELECT * FROM SplitWithCount GO
What we want to achieve is to get an output as shown below:
The puzzle here is to see who can bring a simple code to achieve this above requirement.
Interestingly, we have had a note around using STRING_SPLIT function inside SQL Server and it has been documented here: SQL SERVER 2016 – STRING_SPLIT Function Performance Comparison – Notes from the Field #118. If you are on SQL Server 2016 then we can easily do this using the following statement.
SELECT ss.value AS SplitMyString, COUNT(ss.value) [Counts] FROM SplitWithCount CROSS APPLY STRING_SPLIT(cols,';') ss GROUP BY ss.value GO
Though this is a very elegant way to do inside SQL Server 2016, I don’t think we can even beat the simplicity of this solution. Having said that if you are on an earlier version, would love to see what solutions you can come up with. Let me know. If you go through my earlier blog this would look like:
SELECT SplitMyString, COUNT(*) Counts FROM ( SELECT CAST(CONCAT('<Cols>' , REPLACE(cols, ';' , '</Cols><Cols>' ) , '</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
Leave a comment if you are aware of alternate solutions. I will be happy to publish that on blog with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)