Selecting a substring of a column created with a recursive query

Question

I have a recursive CTE query that constructs a file path from from a folder structure within an application called HP Quality Center - what I'm trying to do is simply de-construct the string to a certain folder length based upon a symbol '\'.

Could some explain as to whether this is possible with rows that doesn't actually 'exist' so to speak, i.e. as the query builder comes back saying that the column name is blank when I try to use the substring and charindex functions.

Many Thanks

with cycle_path (cf_item_id, cf_item_name, cf_path) 
as 
(
  -- set up base
  select 0, CONVERT(varchar(255),'Root'), CONVERT(varchar(255),'Root')

  union all

  -- determine path recursively
  select cf.cf_item_id, cf.cf_item_name, CONVERT(varchar(255), cp.cf_path +     '\' + cf.cf_item_name)
  from cycl_fold cf
        inner join cycle_path cp on cf.cf_father_id = cp.cf_item_id
)

select SUBSTRING(cp.cf_path, 0, CHARINDEX('\', cp.cf_path, CHARINDEX('\', cp.cf_path, CHARINDEX('\', cp.cf_path, CHARINDEX('\', cp.cf_path, 0) + 1) + 1) + 1)), cp.cf_path
from cycle_path cp
        inner join cycle cy on cp.cf_item_id = cy.cy_folder_id
where cp.cf_path LIKE 'Root\%'
order by cp.cf_path

When you `SELECT *` instead of your substring, do you get a valid path? There should be no problem performing manipulations on the results of a recursive cte, the behavior should be the same as getting records from any other table. — Hart CO, Feb 17 '15 at 17:37
Do you have an example of a value for cf_path that your substring isn't working as expected for? — Tab Alleman, Feb 17 '15 at 18:26
Your query looks fine to me, you don't have an alias on the `SUBSTRING(...)` field in your final `SELECT`, but that shouldn't be a problem unless you're using the above to create a view/table. — Hart CO, Feb 17 '15 at 18:36
@TabAlleman Here's a resulting file path that is created, each segment is in essence it's own 'item' - the way the Quality Centre db is setup I have to do this method in order to compile a meaningful filepath. Root\ProjectName\TestCycle\Department\TestSet\TestName — cstones88, Feb 18 '15 at 07:47
I should have asked this as well: When I run your substring on the sample you provided, it returns "Root\ProjectName\TestCycle\Department". What value are you trying to get? — Tab Alleman, Feb 18 '15 at 13:51
@TabAlleman the subtring query was supposed to return everything to the left of the third file path segment. Root/ProjectName/TestCycle would be the expected outcome, — cstones88, Feb 19 '15 at 09:22

score 1 · Answer 1 · answered Feb 19 '15 at 14:01

You've put one too many charindex functions. To get everything to the left of the third backslash, you should use 3 charindexes. You've used 4.

Shorten your substring to this to get the desired result:

select SUBSTRING(cp.cf_path, 0, CHARINDEX('\', cp.cf_path, CHARINDEX('\', cp.cf_path, CHARINDEX('\', cp.cf_path, 0) + 1) + 1))

Selecting a substring of a column created with a recursive query

1 Answers1