Tuesday, November 26, 2013

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:

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
                           insert into Hierarchy (StageLevelName, StageLevel)    
                           values (@array_value, @Level)
                           insert into Hierarchy (StageLevelName, StageLevel, ParentID)
                           values (@array_value, @Level, @ParentId)
                     set @ParentId = @@IDENTITY
                     select @ParentId
                     --Moving to end of array
                     SELECT @StageLevel = STUFF( @StageLevel, 1, @separator_position, '')

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