Number2 Blog - myITforum

At the Minneapolis area System Center User Group meeting last night, I mentioned the notion of using LIKE and the bracket wildcard [] to match a range of version numbers (which incidentally works for versions, IP Addresses, IP Subnets, etc.) instead of using greater than or less than because of the way WQL/SQL does a dictionary sort, not a numerical sort.

I mentioned that I had written an article describing how to do that, but that it was embedded in a broader article on something else. 

In case anyone's looking for it, here's the article http://myitforum.com/cs2/blogs/jnelson/archive/2008/06/04/118136.aspx

Otherwise, here's the relevant excerpt from that article:

---------------------------------------------------------

What we're trying to do is find REPDRVFS.DLL with versions like

5.1.2600.3138%
5.2.3790.2936%
5.2.3790.4080%

* but we'll also consider a version as having the update if the last 4 digits are higher than the last 4 digits in these:

5.1.2600.3138%
5.2.3790.4080%

Consider the case of XP SP3.  The version of that file on SP3 is 5.1.2600.5512%.  Well, I don't want to specify every possible version that could exist between 3138 and 5512 one-at-a-time, so we'll use a wildcard expression involving brackets and numbers to get us the results we want.

By the way, you might be tempted to use sf.FileVersion > '5.1.2600.3138' ... don't be.  This is a varchar field not a numeric field.  SQL will perform a dictionary sort not a numeric sort so you can run into all kinds of problems if you're trying to use greater than with the file version like that. 

So the options are, extract the string and convert it to a number and deal with that (I mention that tactic HERE), or use some fancy wildcards.  I've already demonstrated the string extraction method in a previous article so I'd like to mention the wildcard method.  Plus in this case, having to do a bunch of function calls and string manipulation for every record would probably be a lot more work for the SQL engine than adding some fancy wildcards because SQL is built for pattern matching and is very efficient at it compared to the overhead involved in function calls and string manipulation.

Let's quickly talk about wildcards...a wildcard character you're probably all familiar with is the % sign.  It basically means match 0 or more of any characters.  Then there's the underscore _ which means match any single character.  But there's also these brackets [ ] which let you specify a range (where [0-9] means a single character matching the number 0 through 9 or [a-g] means a single character matching any letter from a through g) or a set (where [135] means a single character matching a 1, 3 or 5 and [adf] means any single character matching a, d or f).  You can also put those brackets next to each other to specify multiple ranges (like [0-9][0-9] representing every number between 00 and 99].  I think that bracket wildcard can come in handy.  

Well then, let's take our file version example above and try to use these wildcards.  We know that version 5.1.2600.3138% indicates the update is installed, but so does 5.1.2600.3139%, 5.1.2600.3140%, and every other number up to 5.1.2600.9999%.  So, we need a way to represent 3138 and every number up to 9999.

[0-9][0-9][0-9][0-9] would represent every number from 0000 to 9999 which is too many.
[3-9][1-9][3-9][8-9] would represent 3138 to 9999, but miss 0-7 in the 1's column, 0-2 in the 10's column and 0 in the 100's column, which isn't enough.

So let's start with 3138.  If we start all the way on the right and use a wildcard to roll the 1's over like an odometer which starts at 3138, we'll get

sf.FileVersion LIKE '5.1.2600.313[8-9]%'  

which matches 3138 and 3139.  Then we move to the left and put a wildcard there too to roll the 10's and 1's over starting at the next number (3140)

sf.FileVersion LIKE '5.1.2600.31[4-9][0-9]%'

which matches 3140 to 3199.  Then we move to the left again and put a wildcard to roll the 100's 10's and 1's over starting at the next number (3200)

sf.FileVersion LIKE '5.1.2600.3[2-9][0-9][0-9]%'

which matches 3200 to 3999.  Now we do it one more time to the left and roll the 1000's, 100's, 10's and 1's over starting at the next number (4000)

sf.FileVersion LIKE '5.1.2600.[4-9][0-9][0-9][0-9]%'

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog