Need 8 characters form a lengthy RegKey path - Invalid length parameter passed to the LEFT or SUBSTRING

Question

I have been working with a guy to finish off a rather ingenious means by which to extract information on packages installed by SCCM 2012 vs the built-in inventoried "Programs and Features". The last piece is extracting the PACKAGEID from registry strings that have been inventoried in the aforementioned process. Each string looks like this (the target "PACKAGEID" is identified in bold: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Mobile Client\Software Distribution\Execution History\System\ LAB00003 \ac80c725-7dc7-11e5-9bc8-000c292d4525

As stated, i am not the genius behind any of this but i wanted to understand why i am getting the following error:

Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.

From the following query:

SELECT DataType0, KeyPath0, Name0, Value0, ( SELECT SUBSTRING(KeyPath0, LEN(LEFT(KeyPath0, CHARINDEX ('\System\', KeyPath0))) + 1, LEN(KeyPath0) - LEN(LEFT(KeyPath0, CHARINDEX ('\System\', KeyPath0))) - LEN(RIGHT(KeyPath0, LEN(KeyPath0) - CHARINDEX ('\', KeyPath0))) - 1) ) as "Package ID" FROM dbo.v_GS_Registry_Values0

i verified that the the dbo.v_GS_Registry_Values0 view does indeed have the reg key string in it via select * from SCCM_Ext.vex_GS_Registry_Values0 but despite tons of searching, my simple sql mind cannot make sense of the query and its use of LEN & CHARINDEX.

Totally throwing myself at the mercy of this site in hopes i could get not only the resolution to this but also a better understanding of why this is happening and how the query works.

if there is ANY additional information i could provide please let me know.

Answer 1

If you're just trying to get the next string after \System\ you're SQL is quite complex, you can do it just with this:

SELECT left (Y.S, charindex ('\', Y.S) - 1)
from Table1
outer apply (
 select CHARINDEX ('\System\', KeyPath0) as pos
) X
outer apply (
 select substring (KeyPath0, X.pos + 8, 9999) as S
) Y

Example in SQL Fiddle

The first outer apply finds the \System\ the second gets the rest of the string (assuming max path is 9999 characters) and then just take the part before the next \ in the actual select.