[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Get the Child Nodes from the Path

Hi Experts,

   
This query below will only fetch the Parent and the Immediate child.

$ is the delimiter in the path.

Requirement :  Here in the Path_OU , I want to get all the childs in the path including the leaf node (90409,90406)
 
Please edit my query below to get the child nodes 90409,90406. Will appreciate your help.

SELECT path_ou,
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), 1) - 1) 'Parent',
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), 1) - 1) 'Child1'
            
            FROM ARMD_Organization_Unit
            where path_ou='$90409$90406$90250$694$'


Regards,

SRK
0
n_srikanth4
Asked:
n_srikanth4
  • 2
1 Solution
 
sachitjainCommented:
Here you go

SELECT path_ou,
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), 1) - 1) 'Parent',
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), 1) - 1) 'Child1',
            replace(SUBSTRING(SUBSTRING(path_ou, 1, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 1)), 2, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) + 1)), '$', ',') 'Children'
FROM ARMD_Organization_Unit
where path_ou='$90409$90406$90250$694$'
0
 
n_srikanth4Author Commented:
Sorry for the confusion.

Here I want to always get the leaf node which is always start at the  right side of the Path_OU.
Here I want to get the value 90409 only , which is the leaf node.

'$90409$90406$90250$694$'
0
 
sachitjainCommented:
That's even more simple

SELECT path_ou,
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,2) - 2), LEN(path_ou)), 1) - 1) 'Parent',
            LEFT(SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), CHARINDEX('$', SUBSTRING(path_ou, LEN(path_ou) - (CHARINDEX('$', REVERSE(path_ou) ,CHARINDEX('$', path_ou, 2)) - 2), LEN(path_ou)), 1) - 1) 'Child1',
            SUBSTRING(@path_ou, 2, CHARINDEX('$', @path_ou ,2)-2) 'LEAF'
FROM ARMD_Organization_Unit
where path_ou='$90409$90406$90250$694$'
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now