I needed to do this so here's what I came up with. I'm quite proud of this SQL statement so had to share. Since we can't use variables the charindex functions have to be repeated a gazillion times. Caution: It really bogs down the report editor.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Convert(int,SubString(tblSoftware.softwareVersion, 1, CharIndex('.',
tblSoftware.softwareVersion) - 1)) * 10000000 +
Convert(int,SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1, CharIndex('.', tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1) - CharIndex('.',
tblSoftware.softwareVersion) - 1)) * 1000000 + Convert(int,Case
When CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1) + 1) = 0 Then SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1) + 1, Len(tblSoftware.softwareVersion) - CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1))
Else SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1) + 1, CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1) + 1) - CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) + 1) - 1)
End) * 100 + Case
When CharIndex('.', tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion) +
1) + 1) = 0 Then 0
Else Convert(int,SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1) + 1) + 1,
Len(tblSoftware.softwareVersion) - CharIndex('.',
tblSoftware.softwareVersion, CharIndex('.', tblSoftware.softwareVersion,
CharIndex('.', tblSoftware.softwareVersion) + 1) + 1)))
End As VersionNumberInteger
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Java [678]%' And tblAssetCustom.State =
1