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.
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 (http://blog.SQLAuthority.com)