How to build a table in SQL that describes a binary tree by a string that describes it using brackets?

Question

SQL

I'm doing a lab on MS SQL, I need to write some kind of tabular multi-operator function.
I came up with a problem: from a string of the form 'one (two (three (four ( ) five ( )))) (six ( ))', which represents a binary tree, in the nodes of which there are strings, build a table.
I want to get a table like
item parent level
one NULL 0
two one 1
six one 1
three two 2
and so on.
How to approach the solution? The question is how to use SQL.

Answer 1

--3) многооператорная табличная функция
-- построить бинарное дерево из строки
IF OBJECT_ID (N'dbo.BuildBinaryTree') IS NOT NULL
  DROP FUNCTION dbo.BuildBinaryTree
GO
CREATE FUNCTION dbo.BuildBinaryTree(@TreeStr nvarchar(max))
RETURNS @Tree table (ItemID int NOT NULL PRIMARY KEY, Name nvarchar(max) NOT NULL, ParentID int, Level int)
BEGIN
  DECLARE @curr_pos int;	-- итератор строки

  SET @curr_pos = CHARINDEX('(', @TreeStr, 0)

  IF @curr_pos = 0
  BEGIN
    INSERT @Tree (ItemID, Name, ParentID, Level) VALUES (1, LTRIM(RTRIM(@TreeStr)), NULL, 0)
    RETURN
  END
  ELSE
    INSERT @Tree (ItemID, Name, ParentID, Level) VALUES (1, LTRIM(RTRIM(SUBSTRING(@TreeStr, 1, @curr_pos - 1))), NULL, 0)

  DECLARE @curr_id int = 1
  DECLARE @curr_level int = 0

  WHILE (@curr_pos < LEN(@TreeStr))
  BEGIN
    DECLARE @nearest_open int = CHARINDEX('(', @TreeStr, @curr_pos)
    DECLARE @nearest_close int = CHARINDEX(')', @TreeStr, @curr_pos)

    IF @nearest_open < @nearest_close AND @nearest_open > 0
    BEGIN
      DECLARE @tmp_parent_id int
      SELECT @tmp_parent_id = MAX(ItemID) FROM @Tree WHERE Level = @curr_level

      SET @curr_pos = @nearest_open + 1
      SET @curr_level = @curr_level + 1
      SET @curr_id = @curr_id + 1

      DECLARE @tmp_nearest_open int = CHARINDEX('(', @TreeStr, @curr_pos);
      DECLARE @tmp_nearest_close int = CHARINDEX(')', @TreeStr, @curr_pos);

      IF @tmp_nearest_open < @tmp_nearest_close AND @tmp_nearest_open > 0
      BEGIN
        INSERT @Tree (ItemID, Name, ParentID, Level) VALUES (@curr_id, LTRIM(SUBSTRING(@TreeStr, @curr_pos, @tmp_nearest_open - @curr_pos)), @tmp_parent_id, @curr_level)
        SET @curr_pos = @tmp_nearest_open
      END
      ELSE
      BEGIN
        INSERT @Tree (ItemID, Name, ParentID, Level) VALUES (@curr_id, LTRIM(SUBSTRING(@TreeStr, @curr_pos, @tmp_nearest_close - @curr_pos)), @tmp_parent_id, @curr_level)
        SET @curr_pos = @tmp_nearest_close
      END
    END
    ELSE
    BEGIN
      SET @curr_pos = @nearest_close + 1
      SET @curr_level = @curr_level - 1
    END
  END

  RETURN
END
GO

SELECT * FROM dbo.BuildBinaryTree('hello')
SELECT * FROM dbo.BuildBinaryTree('hello (fisrt)')
SELECT * FROM dbo.BuildBinaryTree('hello(fisrt)(second)')
SELECT * FROM dbo.BuildBinaryTree('hello (fisrt (second))')
SELECT * FROM dbo.BuildBinaryTree('hello (fisrt (second  (third (fourth (fifth)))))')
SELECT * FROM dbo.BuildBinaryTree('hello (fisrt (second (third (fourth (fifth))))) (by)')
SELECT * FROM dbo.BuildBinaryTree('hello (world (my (pi (put)) (beer)) (by (pie (he)) (boy))) (hi)')
GO

Answer 2

The question is how to use SQL.

Apparently it is necessary to learn the theoretical part and read the documentation.