SCCM 2007 - Retrieve Only the Data to the Left of a Symbol

I see a column within a certain view containing data like this:

Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition1

I want to collect only that data left of the "|" symbol for my report.

I have surfed around a littel bit and cannot find how to do this.

Thank you.
LVL 3
isd503Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
$a= "Microsoft Windows XP Professional|C:\WINDOWS|\Device\Harddisk0\Partition1"

$a.Split("|")[0]  returns the first part

$a.Split("|")[1]  returns the second part

$a.Split("|")[2]  returns the third part
0
dsackerContract ERP Admin/ConsultantCommented:
If you're wanting to do this in your query:

SELECT SUBSTRING(yourcolumn, 1, CHARINDEX('|', yourcolumn) - 1) AS YourColumnName
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
isd503Author Commented:
That worked great!  Thank you.  The purpose was to determine if R2 was installed onto a server.  I will post the statement below.
0
isd503Author Commented:
This is for an inventory report in SCCM:

SELECT DISTINCT 
	SYS.Name0 AS [Computer Name], 

	[Environment] = CASE
		WHEN (SYS.Name0 LIKE '%DV%')
		THEN 'DEV'
		WHEN (SYS.Name0 LIKE '%UA%')
		THEN 'UAT'
		ELSE 'PROD'
	END,

	SYS.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 

	SUBSTRING(MEM.Name0, 1, CHARINDEX('|', MEM.Name0) - 1) AS [Operating System],

	OS.CSDVersion0 AS [Service Pack], 
	DATEDIFF (day, OS.LastBootUpTime0, WS.LastHWScan) AS [Uptime (in Days)], 
	CONVERT(varchar(20), OS.LastBootupTime0, 100) AS [Last Reboot Date/Time],
	CONVERT(varchar(20), WS.LastHWScan, 100) AS [Last Hardware Inventory],

	[Serial Number] = CASE
		WHEN (ENCL.SerialNumber0 = 'None')
		THEN 'VM'
		ELSE ENCL.SerialNumber0
	END,

	COMP.Manufacturer0 AS [Manufacturer], 
	COMP.Model0 AS [Model],

	[Time Zone] = CASE
		WHEN COMP.CurrentTimeZone0 = '0'
		THEN 'UTC'
		WHEN COMP.CurrentTimeZone0 = '-240'
		THEN 'Eastern'
		WHEN COMP.CurrentTimeZone0 = '-300'
		THEN 'Central'
		WHEN COMP.CurrentTimeZone0 = '-360'
		THEN 'Mountain'
		WHEN COMP.CurrentTimeZone0 = '-420'
		THEN 'Pacific'
		ELSE 'Unknown'
	END,		

	MEM.TotalPhysicalMemory0 / 1000000 As [Physical Memory (GB)], 
	CPU.Name0 AS [Processor Name], 
	CPU.NumberOfCores0 AS [Number of Cores],
	CPU.NumberOfLogicalProcessors0 As [Number of Logical CPUs],

	[Processor Type] = CASE
		WHEN CPU.ADDRESSWIDTH0 = '32' 
		THEN '32-Bit' 
		ELSE '64-Bit' 
	END,

	[64 Bit Supported] = CASE
		WHEN CPU.IS64BIT0 = '1' 
		THEN 'Yes' 
		ELSE 'No' 
	END,

	LDSK_C.Size0 / 1024 AS [C: Size (GB)],
	LDSK_C.FreeSpace0 / 1024 AS [C: FreeSpace (GB)],
	(LDSK_C.Size0 / 1024) - (LDSK_C.FreeSpace0 / 1024) AS [C: Used (GB)],

	LDSK_E.Size0 / 1024 AS [E: Size (GB)],
	LDSK_E.FreeSpace0 / 1024 AS [E: FreeSpace (GB)],
	(LDSK_E.Size0 / 1024) - (LDSK_E.FreeSpace0 / 1024) AS [E: Used (GB)]
	    
FROM v_R_System AS SYS

INNER JOIN v_GS_OPERATING_SYSTEM OS
	ON SYS.ResourceID = OS.ResourceID
INNER JOIN v_GS_WORKSTATION_STATUS WS
	ON SYS.ResourceID = WS.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE_UNIQUE ENCL
	ON SYS.ResourceID = ENCL.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM COMP
	ON SYS.ResourceID = COMP.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY MEM
	ON SYS.ResourceID = MEM.ResourceID
INNER JOIN v_GS_PROCESSOR CPU
	ON SYS.ResourceID = CPU.ResourceID
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_C
	ON SYS.ResourceID = LDSK_C.ResourceID
	AND LDSK_C.DriveType0 = '3'
	AND LDSK_C.Name0 LIKE 'C%'
LEFT OUTER JOIN v_GS_LOGICAL_DISK AS LDSK_E
	ON SYS.ResourceID = LDSK_E.ResourceID
	AND LDSK_E.DriveType0 = '3'
	AND LDSK_E.Name0 LIKE 'E%'

WHERE (WS.LastHWScan <> 0) 
	AND SYS.canonicalName0 LIKE 'mydomain.com/Servers%' OR SYS.canonicalName0 LIKE 
	'mydomain.com/Domain Controllers%' OR SYS.Name0 IN ('MACHINE1',
	'MACHINE2','MACHINE3','MACHINE4','MACHINE5','MACHINE6',
	'MACHINE7','MACHINE8','MACHINE9','MACHINE10')

ORDER BY SYS.Name0 

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
Glad that could help. Nice query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.