SQL greater than & less than statements don't return correct results

Question

I'm running queries and they aren't looking at the entire number when using > and < statements so in this example the results are incorrect as it's looking at the 1 rather than the 10 in the first octet. Here is the query and an excerpt of the results.

select distinct v1.name, v1.[user], t1.displayversion from vComputer v1
inner join Inv_AddRemoveProgram t1 on v1.Guid = t1._ResourceGuid
where t1.DisplayName like 'Symantec Enterprise Vault%' and t1.DisplayVersion < '9.0.13509'

Results:

name            user          displayversion
ATVIEL-PB0225L4 bunger          10.0.17573
ATVIEL-PB0225LH fmotedayen      10.0.17573
ATVIEL-PB036MTR skoeroezsi      10.0.17573
ATVIEL-PB036MTS criesenhuber    10.0.17573
ATVIEL-PC00768L skaehler        10.0.17573

How can I correct this?

Answer 1

The problem is that you are comparing strings, and that will be then done in alphabetical order. You have basically 2 options:

1. Split the version number into parts so that you can convert each of them into numbers and have 3 fields in order by (or whatever the maximum number of parts in the version is)

2. Store the version numbers with leading zeros, so that alphabetical order is the same as numeric order.