Hello and welcome back to another post!
So, recently I was looking at some stored procedures that were executed to generate a report of whatever, the thing is, the report was able to accept parameters, some of them could be NULL and some others gave you the chance to select several categories or products or anything, and you’ll get a comma delimited list like this:
Whenever I see this kind of stored procedures, I know that the developer must have been suffering writing a query to handle these parameters. In some other companies, they managed to create a function with some weird code to split the values and be able to handle easily.
Enter STRING_SPLIT()
I should mention that these stored procedures I was talking about before, were created in versions SQL Server 2014 and before, now starting with version 2016 and after, we don’t have to break our heads writing or using functions that can decrease the performance of our stored procedures and queries.
There is a built-in table valued function called STRING_SPLIT() and, as the name implies, it does splits a string depending on the separator (which you can also define).
Let’s check on an example:
Ok, what’s going on in here? As you can see, I declared a variable and assigned a comma separated list, this will be my “parameter” for now. Since I just wanted to split the list into separate values, I used the STRING_SPLIT function as follows:
You have to pass the parameter or comma (or anything) separated list to the function, and you have to tell it what character you are going to be using as separator, in this case is a comma, but you can use tabs, pipes, and basically anything! The only rule is that it has to be of type char(1), varchar(1), nvarchar(1) or nchar(1).
Are you wondering about that “value” thingy you see there? Let me tell you. I mentioned that STRING_SPLIT is a table valued function, hence, will return a table. In this case, a table with a single column called “value”. If you are still wondering, yes. You can do this…
REAL LIFE EXAMPLE
I wouldn’t call this a real life example, but sometimes you may be faced with something like this. First, let’s create a stored procedure like the one below.
See how we are joining both tables Sales.Orders and Sales.OrderLines with our function STRING_SPLIT and passing the parameter, using the column “value” as the join condition. I needed to cast the value column to integer because, remember that we received a string and the return type will be a string as well. Now, let’s see if it works.
It worked! As you can see, we got the dataset with the order ids we passed into the stored procedure as a comma separated list. Oh, by the way, for this example I’m using WorldWideImporters database and SQL Server 2017.
If you don’t have it, you can download it from here: https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
Also, you can find the code for this and previous posts in the following link: https://github.com/learningsql/sql-code
I hope you guys liked the post and learned something in the way. I can assure you that this function might come in handy someday.
Don’t forget to like and share. See you soon.
Cheers!