Extract hierarchy from String

sql query to get hierarchical tree
sql query to get organization hierarchy
how to create a hierarchy table in sql
sql server parent child hierarchy
hierarchyid
sql server hierarchical query example
oracle hierarchical query example
sql server query to get hierarchical tree

I have a table that contains two columns, one with a code, and the other with a path format:

 Code   Path
 H      A/B/C/G/H
 D      B/L/P/D
 G      A/B/C/G
 R      J/X/R

My goal would be to have, for each code, the list of parent with level like this:

Code  Parent  Level
H     A       1
H     B       2
H     C       3
H     G       4
H     H       5
D     B       1
D     L       2
D     P       3
D     D       4
G     A       1
G     B       2
G     C       3
G     G       4
R     J       1
R     X       2
R     R       3

I try with recursive code, but it only displays the level of current code.

How I can obtain my result?

you can use split by xml + cross apply + row_number group by code order by rowrank to do it.

SELECT Code,
    LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Parent,
    row_number() over (partition by code order by (select 1)) AS Level
FROM
(
    SELECT Code,CAST('<XMLRoot><RowData>' 
       + REPLACE(Path,'/','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    FROM   T
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) 

US9659073B2, Techniques for extracting hierarchical data stored in multiple records, first set of strings, parsing the string to extract one or more hierarchy components from� Extract hierarchy from String. Ask Question Asked today. Active today. Viewed 61 times 1. I have a table that contains two columns, one with a code, and the other

FOR SQL SERVER 2016 and above you can try the below code that uses STRING_SPLIT.

SELECT 
    code, 
    value path,
    row_number() over (partition by code order by (SELECT NULL)) Level
FROM 
    mytable
    CROSS APPLY STRING_SPLIT(path, '/');

CHECK DEMO HERE

Hierarchical Queries, CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. The NOCYCLE parameter instructs Oracle Database to return rows � pandas.Series.str.extract¶ Series.str.extract (* args, ** kwargs) [source] ¶ Extract capture groups in the regex pat as columns in a DataFrame. For each subject string in the Series, extract groups from the first match of regular expression pat. Parameters pat str. Regular expression pattern with capturing groups. flags int, default 0 (no flags)

declare @table1 table (Row1# int,Code nvarchar(max),Path nvarchar(max))
declare @table3 table (code nvarchar,parent nvarchar(max),[Level] nvarchar(max))
Declare @count1 int = 1
Declare @count2 int = 1
declare @var nvarchar(max)
declare @var2 nvarchar(max)


 INSERT INTO @table1
 select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) Row1#,Code,[dbo].[Path].Path  from 
 [dbo].[Path]


select @count2 = count(*) from [dbo].[Path]

WHILE @count1 <= @count2
        BEGIN


             select top 1 @var = path,@var2 = Code  from @table1 
            --select top 1  @var = path from [dbo].[Path] where code = 'H'

            insert into @table3
            select Item,@var2 as parent, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as 'level' from dbo.SplitString(@var,'/')


            delete from @table1 where Row1# = @count1

            set @count1 = @count1 + 1
        end


select * from @table3



ALTER FUNCTION [dbo].[SplitString]
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(MAX)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END

Hierarchical Data (SQL Server), The built-in hierarchyid data type makes it easier to store and query hierarchical data. hierarchyid is optimized for representing trees, which are� There are circumstances you may need to extract excel substring from within a string. You will need the MID function for this. The Syntax of the MID function is: MID(text, start_num, num_chars) Text (Required): The Excel string with the characters you want to extract. Start_num (Required): The position of the first character you want to extract

If you use SQL Server 2016+, one possible solution is to use a JSON-based approach with one string transformation. You need to transform the data in each row into a valid JSON array (A/B/C/G/H into ["A","B","C","G","H"]) and after that to parse thе data with OPENJSON() and default schema. The result from OPENJSON() is a table with columns key, value and type, and in case of JSON array, the key column holds the index of items in the JSON array:

Note, that using STRING_SPLIT() is not an option here, because the order of the returned rows is not guaranteed.

Table:

CREATE TABLE Data (
   [Code] varchar(1),
   [Path] varchar(100)
)
INSERT INTO Data
   ([Code], [Path])
VALUES
   ('H', 'A/B/C/G/H'),
   ('D', 'B/L/P/D'),
   ('G', 'A/B/C/G'),
   ('R', 'J/X/R')

Statement:

SELECT 
   d.[Code],
   j.[value] AS Parent,
   (j.[key] + 1) AS Level
FROM Data d
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(d.[Path], '/', '","'), '"]')) j

Result:

Code Parent Level
H    A      1
H    B      2
H    C      3
H    G      4
H    H      5
D    B      1
D    L      2
D    P      3
D    D      4
G    A      1
G    B      2
G    C      3
G    G      4
R    J      1
R    X      2
R    R      3

Lesson 1: Converting a Table to a Hierarchical Structure, Since the original table already uses hierarchyid, this query essentially flattens the hierarchy to retrieve the manager of the employee. In� A minor difference here is that we need to extract the characters from the right of the text string. Here is the formula that will do this: =RIGHT(A2,LEN(A2)-FIND("@",A2)) In the above formula, we use the same logic, but adjust it to make sure we are getting the correct string. Let’s again take the example of brucewayne@batman.com.

/*Here you go!!! */

IF OBJECT_ID('TEMPDB..#PATH') IS NOT NULL 
       DROP TABLE #PATH
CREATE TABLE #PATH (PID INT IDENTITY(1,1), CODE NVARCHAR(10), [PATH] NVARCHAR(200))

INSERT INTO #PATH(CODE, [PATH])
SELECT 'H', 'A/B/C/G/H'
UNION
SELECT 'D', 'B/L/P/D'
UNION
SELECT 'G', 'A/B/C/G'
UNION
SELECT 'R', 'J/X/R'


IF OBJECT_ID('TEMPDB..#ExtendedPath') IS NOT NULL 
       DROP TABLE #ExtendedPath
CREATE TABLE #ExtendedPath ( CODE NVARCHAR(10), Parent NVARCHAR(50),  [Level] INT)


DECLARE @StartOutLoop INT = 1
              , @EndOutLoop INT 
              , @StartInLoop INT = 0
              , @EndInLoop INT
              , @Path NVARCHAR(200)

SELECT @EndOutLoop = MAX(PID) FROM #PATH
SELECT * FROM #PATH
WHILE @StartOutLoop <= @EndOutLoop
BEGIN

       SELECT @StartInLoop = 0
       SELECT @Path = [PATH] FROM #PATH WHERE PID = @StartOutLoop 
       SELECT @EndInLoop = LEN([PATH]) - LEN(REPLACE([PATH],'/','')) FROM #PATH WHERE PID = @StartOutLoop 

       WHILE @StartInLoop <= @EndInLoop
       BEGIN
              INSERT INTO #ExtendedPath (CODE, PARENT, [LEVEL])
              SELECT CODE
                      , CASE WHEN CHARINDEX('/',@PATH) >0 then  SUBSTRING(@Path, 0, CHARINDEX('/',@PATH))
                             ELSE @PATH
                             END
              , @StartInLoop+1
              FROM #PATH WHERE PID = @StartOutLoop

              SELECT @Path = SUBSTRING(@Path, CHARINDEX('/',@PATH) + 1,LEN(@PATH))
              --select @Path
              SELECT @StartInLoop += 1
       END

SELECT @StartOutLoop += 1

END
SELECT * FROM #ExtendedPath

Parsing Organizational Hierarchy or Chart of Accounts in Power BI , Because it is not usually clear that how many levels of hierarchy you get, the hierarchy structure is stored in two columns across the table; ID, and� The LEFT function allows you to extract a given number of characters from the left of a given text string. The LEFT function requires two arguments: the text string you are evaluating and the number of characters you need extracted from the left of the text string. In the example, you extract the left five characters from the value in Cell A4.

Exporting a Hierarchy in JSON: with recursive queries, In this article, we're going to use SQL to export the data from our relational model into a JSON document. The trick that makes it complex in this� Method B: Extract substring after or before a defined character by Kutools for Excel. For directly extract substring after or before a specified character, you can use the Extract Text utility of Kutools for Excel, which can help you to extract all characters after or before a character, also can extract specific length of characters before or after a character.

Create Hierarchies, You can also create your own custom hierarchies. For example, if you have a set of fields named Region, State, and County, you can create a hierarchy from these � For Example:We have name in a cell and we want to extract the first word from the cell. To extract the first name from the cell, enter the formula in cell:-=LEFT (A2, 7), function will return“Broncho” RIGHT: Returns the last character(s) in a text string based on the number of characters specified.

GeometryPartExtractor, Extracts selected geometry parts based on a Geometry XQuery. method is to use an XQuery that matches the "Hierarchy Position" to "Leaf Node". types such as attribute references, math and string functions, and workspace parameters. Target Text Extractor is an online app designed to find and extract text surrounded or defined by specific character patterns. It allows you to define the character patterns with standard JavaScript regular expressions and offers a set of auxiliary functions to facilitate the text processing.

Comments
  • What is your SQL Server version?
  • SQL Server 2012 | db<>fiddle
  • Hi Arun, thank you, it works perfectly !! Sorry, it seems I can accept only one solution :(
  • Hi Zhorov,thank you, it works perfectly !! Sorry, it seems I can accept only one solution :(