Search code examples
sqlstringsplit

Split a dynamic string as levels

I have table containing dynamics string that changes in length as per the example below

string 1: A.B.C:D.E.F:G.H.I:J
 string 2: A.B.C:D

I need to split the string to get to the following design :

level_0
     level_1
        level_2
          level_N
             level_Attribute

so for example for String 2 the design will be

level_0: A
     level_1: B
        level_2: C
             level_Attribute: D

I was able to split and create the colunm and assign each value to a level using the following

DECLARE  @str VARCHAR(100)='A.B.C:D.E.F:G.H.I:J';
set @str=REPLACE(@str,':','.')
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@str,'.','","'),'"]]');

SELECT @str                          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(Level_0  VARCHAR(100) '$[0]'
    ,Level_1 VARCHAR(100) '$[1]'
    ,Level_2 VARCHAR(100) '$[2]'
    ,Level_3 VARCHAR(100) '$[3]'
    ,Level_4 VARCHAR(100) '$[4]'
    ,Level_5 VARCHAR(100) '$[5]'
    ,Level_6 VARCHAR(100) '$[6]'
    ,Level_7 VARCHAR(100) '$[7]'
    ,Level_8 VARCHAR(100) '$[8]'
    ,Level_Attribute VARCHAR(100) '$[9]'

    ) ValuesFromTheArray

My problem is when the string is short , the last item is not added to the level_attribute.

Solution

  • There are many ways to solve this problem. I am choosing to leave the level as one big string and the attribute as another string. Lets create the table and insert data.

    -- 1 - create table
create table sample_levels
(
sample_data varchar(128)
);

-- 2 - add data
insert into sample_levels values ('A.B.C:D.E.F:G.H.I:J');
insert into sample_levels values ('A.B.C:D');

    The trick is to convert to comma delimited string and reverse and parse.

    -- get level + attribute
select 
  substring(backwards, 1, charindex(',', backwards) - 1) as attribute,
  reverse(substring(backwards, charindex(',', backwards) + 1, len(backwards) - charindex(',', backwards) )) as levels
from
(
  select 
  reverse(replace(replace(sample_data, '.', ','), ':', ',')) as backwards
  from sample_levels
) as d

    I did this example in SQL Fiddle. Here is the link to play with the SQL code.

    http://sqlfiddle.com/#!18/d16f4/1/0

    enter image description here