Build Hierarchy From Delimited String

In this post we create a parent child hierarchy (n level) from a delimited string like [Parent_Child_LeafChild].

What we are looking for is taking a n length string like "Stage1_Stage2_Stage3" and moving it into a table to build this:

Stage1

--Stage2

----Stage3

DECLARE @separator_position INT -- This is used to locate each separator character

DECLARE @array_value VARCHAR(1000)-- this holds each array value as it is returned

DECLARE @separator char(1) --Used in WHERE clause

declare @StageLevel varchar(255) = 'Stage1_Stage2_Stage3'

declare @holder varchar(255) = @stagelevel

SET @separator = '_' --Separator A.K.A. Delimiter

SET @StageLevel = @StageLevel + @separator --append ',' at the end

-- select PATINDEX('%[' + @separator + ']%', @StageLevel)

Declare @Level int = 0

Declare @ParentId int

  WHILE PATINDEX('%[' + @separator + ']%', @StageLevel) <> 0

      BEGIN -- patindex matches the a pattern against a string

             SELECT @separator_position = PATINDEX('%[' + @separator + ']%',@StageLevel)

             SELECT @array_value = LEFT(@StageLevel, @separator_position - 1) 

                      set @level = @level +1

                     --select @array_value, @level

                     IF @level =1 --This is parent node

                     Begin

                           insert into Hierarchy (StageLevelName, StageLevel)    

                           values (@array_value, @Level)

                     END   

                     ELSE

                     BEGIN

                           insert into Hierarchy (StageLevelName, StageLevel, ParentID)

                           values (@array_value, @Level, @ParentId)

                     END

                     set @ParentId = @@IDENTITY

                     select @ParentId

                     --Moving to end of array

                     SELECT @StageLevel = STUFF( @StageLevel, 1, @separator_position, '')

      END

The end result will be a table with each row as a recursive hierarchy. Plus it has level information for depth.