On 3?16?, ??5?04?, "Neil" <neil...@gmail.com> wrote:
> I am trying to create a report in Microsoft SMS 2003 that displays the
> system name and operating system type e.g. "Microsoft Windows Server
> 2003 R2 Enterprise Edition - hostname". I can get the name ok but have
> a problem the OS type. The following sql returns
>
> Microsoft Windows Server 2003 R2 Enterprise Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 R2 Enterprise Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 R2 Standard Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 Standard Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 Standard Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 Standard Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
> Microsoft Windows Server 2003 Standard Edition|C:\WINDOWS|\Device
> \Harddisk0\Partition1
>
> SELECT TOP 100 PERCENT OPSYS.Name0 AS C054, SYS.Name0
> FROM dbo.v_GS_OPERATING_SYSTEM OPSYS INNER JOIN
> dbo.v_FullCollectionMembership COL ON
> OPSYS.ResourceID = COL.ResourceID INNER JOIN
> dbo.v_GS_COMPUTER_SYSTEM SYS ON OPSYS.ResourceID
> = SYS.ResourceID
> WHERE (COL.CollectionID = 'UL10007A') AND (SYS.Manufacturer0 <>
> 'VMware, Inc.')
> GROUP BY OPSYS.Name0, SYS.Name0
> ORDER BY OPSYS.Name0
>
> How do I trim it so the I am left with "Microsoft Windows Server 2003
> R2 Enterprise Edition" the length of the OS name may change but "|C:
> \WINDOWS|\Device\Harddisk0\Partition1" always stays the same.
your code and output data seems not compare,
i think you can do a trick like this:
--example code
declare @t1 varchar(10)
declare @t2 varchar(10)
select @t1 ='aaa'
select @t2 = ' '
select right(@t2+@t1,10)