First of all, I appreciate it when anyone puts the time and effort into writing and publishing an article, so thank you very much for that. Keep writing.
As you've seen in the discussion above, so far, there are a couple of problems that people have cited and a couple they have not. I realize that some of these aren't even close to the primary thrust of your article but let's check out a couple of options...
First, we need to duplicate the test data you posted for others to test with. I threw in a couple of wild geese just in case such things might occur.
-- DROP TABLE #TempWork
--===== Create the test table and populate it on-the-fly
,('RCX_I04_OLAP 18.104.22.168 [22.214.171.124]')
,('RCX_J01_NoRbar 100.10') --Added the following rows just to be sure
) v (AppVersion)
Should have been an "iSF"
In that latter category, you've built a Scalar UDF, which don't perform well for a lot of reasons we'll not cover in this post. What you should have done is to build it as an iSF (Inline Scalar Function) and, no... I'm not talking about 2019. In this case, an iSF is an iTVF (Inline Table Valued Function) that returns a scalar value. Please see the following article for more about this particular subject.
Here's what the code for that rendition would look like along with an example call. Please DO observe the warning, which other people have also warned about. THIS CODE IS FOR DEMONSTRATION PURPOSES ONLY!!!
DO NOT USE THIS CODE IN PRODUCTION BECAUSE STRING_SPLIT IS NOT GUARANTEED
TO RETURN DATA ELEMENTS IN THE SAME ORDER AS THEY APPEAR IN THE STRING!!!
CREATE FUNCTION dbo.udf_GetBundleVersionUsingStringSplit_JBM01
RETURNS TABLE WITH SCHEMABINDING AS
(--==== Split the string keep only the "first" 3 elements of the split
SELECT TOP 3
StringSplitValue = split.[value]
FROM STRING_SPLIT (@AppVersion,'.') split
)--==== Concatenate the pieces back together as a single string
SELECT BundleVersion = STRING_AGG(StringSplitValue,'.')
--===== Demonstrate the usage of the function
SELECT tw.AppVersion, ca.BundleVersion
FROM #TempWork tw
CROSS APPLY dbo.udf_GetBundleVersionUsingStringSplit_JBM01(tw.AppVersion) ca
I liked the code that @ aklt posted. He used cCTEs (Cascading CTEs ) to DRY out the code. It, too, could be used in an iSF for reuse.
It IS a real shame...
It IS a real shame that Microsoft stuffed up what would have been a great splitter function by not including the ordinal position of the elements returned in the STRING_SPLIT function. If they did, the code would be super simple and you wouldn't even need to think about creating a function for the problem you've portrayed. I'm using the DelimitedSplit8K function to substitute as a better splitter that does return ordinal positions of the split out elements just to show what "could be". Notice the "sort" being done by the STRING_AGG()...
,BundleVersion = STRING_AGG(split.Item,'.') WITHIN GROUP (ORDER BY split.ItemNumber ASC)
FROM #TempWork tw
CROSS APPLY dbo.DelimitedSplit8K(AppVersion,'.') split --This takes the place of STRING_SPLIT()
WHERE split.ItemNumber <=3
GROUP BY tw.AppVersion
I recommend that you replace your current code with what @ aklt posted because it IS guaranteed to return everything in the correct order where yours is not. You can convert his code to an iSF for reasons of performance, as well.
Again, nice job on the article. Keep writing.
You can get the DelimitedSplit8K function at the bottom of the following article in resources section. It works in all versions of SQL Server from 2005 and up.
If you have 2012 or up, a good friend of mine (Eirikur Eiriksson) made even that twice as fast by using LEAD, which first appeared in 2012. His rendition of the function is located in the first section of the following article...
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)