Alex Serov

--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