tblOperatingsystem.Caption Laptop OS version

[MarkJonN]

Hello, May i know how to remove the Microsoft Windows 10 and leave only the Professional, Enterprise and Pro in tblOperatingsystem.Caption OS Version. I Tried this report below but Microsoft Windows 10 still showing in my query report.

Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysOS.OSname As [OS Name],
tblOperatingsystem.Caption As [OS Version],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Inner Join tblPortableBattery On
tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
tblAssets.Assettype = -1
Order By tblAssets.AssetName


See the 2nd picture i attached, i want my report same like that.

Any help will do, Thank you!!! Team LS!

[MarkJonN]

It's all working, You are good man! Thanks a lot!! Stay Safe!

[RCorbeil · Accepted solution]

Keeping it clean and legible,

  CASE
    WHEN tsysOS.OSname = 'Win 7' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 7 ', '')
    WHEN tsysOS.OSname = 'Win 8' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8 ', '')
    WHEN tsysOS.OSname = 'Win 8.1' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 8.1 ', '')
    WHEN tsysOS.OSname = 'Win 10' THEN Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '')
    ELSE tblOperatingsystem.Caption
  END AS [OS Version],

If you don't care so much about legibility, you could nest Replace():

Replace(Replace(Replace(Replace(Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', ''), '10 ', ''), '7 ', ''), '8 ', ''), '8.1 ', '') As [OS Version],

As one who has to come back to code and modify things later, I am not suggesting doing this, but because you don't seem to have used Replace() before, I'm throwing it out there just to show that it can be done.

Unpacking it for illustration:

/* 5 */  Replace(
/*   4 */        Replace(
/*     3 */              Replace(
/*       2 */                    Replace(
/*         1 */                          Replace(tblOperatingsystem.Caption, 'Microsoft Windows ', '')
/*       2 */                            , '10 ', '')
/*     3 */                      , '7 ', '')
/*   4 */                , '8 ', '')
/* 5 */          , '8.1 ', '') As [OS Version],

First, because it's common to all of them, strip out "Microsoft Windows ".
Second, if there's a "10 ", remove that.
Third, if there's a "7 ", remove that.
Fourth, if there's an "8 ", remove that.
Fifth, if there's an "8.1 ", remove that.

Again, do not do this. It does work. There's a time and a place for it. This isn't it.

[MarkJonN]

@RC62N It's working for Windows 10.

Upon checking on my report we have Windows 7? Windows 8 & Windows 8.1? how can i change that as well?

Thank you!!

[RCorbeil]

Select Top 1000000
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysOS.OSname As [OS Name],
  Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '') As [OS Version],
  tblAssets.Lastseen
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
  Inner Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where
  tblAssets.Lastseen <> ''
  And tblAssetCustom.State = 1
  And tblAssets.Assettype = -1
Order By
  tblAssets.AssetName

[MarkJonN]

Thanks for that. However, Do you have a sample of full report? i'm having an error when inserting the replace code on the query.

[RCorbeil]

Refer to the SQL Replace() function. (W3Schools, Microsoft)

In your case, change your OS Version field:

Replace(tblOperatingsystem.Caption, 'Microsoft Windows 10 ', '') As [OS Version],

Here you're saying "search the Caption field for the text 'Microsoft Windows 10 ' and, if found, replace it with no text". Note the trailing space after "Windows 10".