SWQL Trimming/removing part of a string in a select statement

EddyBoi479

Hi all,

I have a SWQL query to get my VMware datastore serial numbers, vcenter servers, cluster and the vendor, i ideally need to strip out everything in the vendor column after the first '(' but cant get it to work, im fairly new to SQL/SWQL, so any advise would be amazing, SWQL query is below:

SELECT d.Clusters.DataCenter.VCenter.DisplayName as vCenterName, d.clusters.DisplayName as ClusterName,d.name as Volume, l.LunID, SUBSTRING(l.CanonicalName,13,24) as SerialNumber,
l.Name as Vendor
FROM Orion.VIM.Datastores d
LEFT JOIN Orion.VIM.Luns l ON d.DataStoreID = l.DataStoreID
where (l.CanonicalName is not NULL
or l.CanonicalName != ''
)
and l.name not like '%local%'
and l.name not like '%HP Serial Attached SCSI Disk%'

EddyBoi479

Nevermind, have this working now with the below:

SELECT d.Clusters.DataCenter.VCenter.DisplayName as vCenterName, d.clusters.DisplayName as ClusterName,d.name as Volume, l.LunID, SUBSTRING(l.CanonicalName,13,24) as SerialNumber,

 

SUBSTRING(l.Name, 0, CHARINDEX(' (', l.Name)) AS Vendor

 

FROM Orion.VIM.Datastores d
LEFT JOIN Orion.VIM.Luns l ON d.DataStoreID = l.DataStoreID
where (l.CanonicalName is not  NULL
or l.CanonicalName != ''
)
and l.name not like '%local%'
and l.name not like '%HP Serial Attached SCSI Disk%'