For Part 2 of the Handy SQL Server Function Series I decided to tackle parsing useful information from the @@VERSION function, because I am an idiot.  It turns out I was confused about CHARINDEX() vs. PATINDEX() and it pretty much invalidated my original solution.  All is not lost though, this mistake turned out to be informative for me, and hopefully for you.

Referring back to the "Version" view in the prelude I started with the following query to extract the version number:

SELECT DISTINCT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, 12) VerNum
FROM VERSION

I used PATINDEX() to find the first hyphen "-" character in the string, since the version number appears 2 positions after it, and got these results:

SQLVersion  VerNum
----------- ------------
2000        8.00.2055 (I
2005        9.00.3080.00
2005        9.00.4053.00
2008        10.50.1600.1

As you can see it was good enough for most of the values, but not for the SQL 2000 @@VERSION.  You'll notice it has only 3 version sections/octets where the others have 4, and the SUBSTRING() grabbed the non-numeric characters after.  To properly parse the version number will require a non-fixed value for the 3rd parameter of SUBSTRING(), which is the number of characters to extract.

The best value is the position of the first space to occur after the version number (VN), the trick is to figure out how to find it.  Here's where my confusion about PATINDEX() came about.  The CHARINDEX() function has a handy optional 3rd parameter:

CHARINDEX (expression1 ,expression2 [ ,start_location ] )

While PATINDEX():

PATINDEX ('%pattern%',expression )

Does not.  I had expected to use PATINDEX() to start searching for a space AFTER the position of the VN, but it doesn't work that way.  Since there are plenty of spaces before the VN, I thought I'd try PATINDEX() on another character that doesn't appear before, and tried "(":

SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString))
FROM VERSION

Unfortunately this messes up the length calculation and yields:

SQLVersion  VerNum
----------- ---------------------------
2000        8.00.2055 (Intel X86) 
    Dec 16 2008 19:4
2005        9.00.3080.00 (Intel X86) 
    Sep  6 2009 01:
2005        9.00.4053.00 (Intel X86) 
    May 26 2009 14:
2008        10.50.1600.1 (Intel X86) 
    Apr
2008        10.50.1600.1 (X64) 
    Apr 2 20

Yuck.  The problem is that PATINDEX() returns position, and SUBSTRING() needs length, so I have to subtract the VN starting position:

SELECT SQLVersion, SUBSTRING(VersionString,PATINDEX('%-%',VersionString)+2, PATINDEX('%(%',VersionString)-PATINDEX('%-%',VersionString)) VerNum
FROM VERSION

And the results are:

SQLVersion  VerNum
----------- --------------------------------------------------------
2000        8.00.2055 (I
2005        9.00.4053.00 (I
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Ummmm, whoops.  Turns out SQL Server 2008 R2 includes "(RTM)" before the VN, and that causes the length to turn negative.

So now that that blew up, I started to think about matching digit and dot (.) patterns.  Sadly, a quick look at the first set of results will quickly scuttle that idea, since different versions have different digit patterns and lengths.

At this point (which took far longer than I wanted) I decided to cut my losses and redo the query using CHARINDEX(), which I'll cover in Part 2.2. 

So to do a little post-mortem on this technique:

1. PATINDEX() doesn't have the flexibility to match the digit pattern of the version number;
2. PATINDEX() doesn't have a "start" parameter like CHARINDEX(), that allows us to skip over parts of the string;
3. The SUBSTRING() expression is getting pretty complicated for this relatively simple task!

This doesn't mean that PATINDEX() isn't useful, it's just not a good fit for this particular problem.  I'll include a version in the next post that extracts the version number properly.

UPDATE: Sorry if you saw the unformatted version of this earlier, I'm on a quest to find blog software that ACTUALLY WORKS.