STRING_SPLIT is to be used with what?

My friends! Last time together, we discussed using the STRING_SPLIT function and how it’s used in combination with the CROSS APPLY.

First off, most of us are used to working with an INNER JOIN instead of CROSS APPLY. Well, you’re not going to be able to use an INNER JOIN when you’re using the STRING_SPLIT function.

For example, in our previous post, we used the following code to split out data:

SELECT id, value
FROM dbo.CommaValues cv
CROSS APPLY STRING_SPLIT(cv.ZipCodeData, ',')

Now, if we attempt to rewrite that to use an INNER JOIN, we get the following:

If you do attempt to use the INNER JOIN, you’ll notice that the alias z only has z.Value in it and the alias cv has cv.id and cv.ZipCodeData as the allowed fields. Helpful – not so much. There’s truly nothing to match on so the INNER JOIN isn’t very helpful.

If we want to make sure that we don’t actually display any blank rows – which is an option that most of us will be asked to do at least once in our career – we’ll need to do the following:

SELECT id, value
FROM dbo.CommaValues cv
OUTER APPLY STRING_SPLIT(cv.ZipCodeData, ',')
WHERE TRIM(value) <> '';

Always make sure to use the TRIM function in your WHERE clause. You’ll need to do this or you could end up with multiple blank rows – because the data can have spaces in it. Therefore, be kind to yourself – include the TRIM function.

Now, if we don’t want to use the CROSS APPLY operator, we can use its friend function – OUTER APPLY.

SELECT id, value
FROM dbo.CommaValues cv
OUTER APPLY STRING_SPLIT(cv.ZipCodeData, ',')

This gives us a result of 306 rows – the exact amount that the CROSS APPLY provided. Now, there is a difference that we will go over in our next post.

Until then…