Splitting a multi-dimensional Array in SQL Server 2012

  • Slady

    SSC Veteran

    Points: 218

    Hello, I have data from an Orders table that has 4 columns, in which 2 contain data in a multi-dimensional array (ClassAttendees and ClassTickets). I am having trouble splitting the array in SQL server 2012. I would like to create a child table from the array that referenced the original OrderId, and held Class Attendee and Class Ticket number.

    The array data is formatted as follows;

    a:3:{i:3327;a:1:{i:0;s:12:"Andrew Jones";}i:3353;a:1:{i:0;s:12:"Andrew Jones";}i:3283;a:1:{i:0;s:12:"Andrew Jones";}}

    a:3:{i:0;s:14:"3374-3373-3329";i:1;s:14:"3375-3373-3355";i:2;s:14:"3376-3373-3284";}

    Example

    a:2:{i:3184;a:2:{i:0;s:11:"Brian Kelly";i:1;s:11:"Peter White";}i:3195;a:2:{i:0;s:11:"Brian Kelly";i:1;s:11:"Peter White";}}

    a:2:{ - 1st array with 2 elements (Classes Attended)

    i:3184; - Id associated with element (Class) in array (3184)

    a:2:{ 2nd array with 2 elements (Ticket Holders for each class)

    i:0;s:11:"Brian Kelly"; - first element of 2nd array 11 characters in length, value = "Brian Kelly"

    i:1;s:11:"Peter White"; - second element of 2nd array 11 characters in length, value = "Peter White"

    }end of first array, first

    I have tried to search the web on how to split a multi-dimensional array in sql, but I cannot find an example that shows me how to deal with this array type within the limitations of SQL 2012. I could not determine a consistent delimiter in this array to separate the components so I tried to use replace, substring and charindex to reformat the array, as well as a split string function but cannot get rid of all the extra array characters like string length (s:10).

    Lastly I tried the below loop (updated from a web resource) but if someone did not enter a ticket name, or had a name with only 1 character, no data is returned. If I amend the If statement to length>0 I end up with additional rows that I have to

    --Create Test Orders Table

    CREATE TABLE [Orders](
    [OrderId] [int] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [ClassAttendees] [nvarchar](200) NULL,
    [ClassTickets] [nvarchar](200) NULL,
    );

    --Insert Test Data Into Orders Table
    Insert into Orders
    Values (2429,'2018-09-28 13:42:35.000','a:1:{i:2427;a:1:{i:0;s:0:"";}}','a:1:{i:0;s:14:"2430-2429-2428";}');

    Insert into Orders
    Values (2844,'2018-12-07 11:04:35.000','a:1:{i:2473;a:1:{i:0;s:11:"Elmer Drupp";}}','a:1:{i:0;s:14:"2845-2844-2604";}');

    Insert into Orders
    Values (3152,'2019-01-29 10:20:21.000','a:1:{i:3083;a:4:{i:0;s:11:"Shiela King";i:1;s:11:"Kevin Smith";i:2;s:10:"Lou Singer";i:3;s:12:"David Fromer";}}','a:4:{i:0;s:15:"3153-3152-3085A";i:1;s:15:"3153-3152-3085B";i:2;s:15:"3153-3152-3085C";i:3;s:15:"3153-3152-3085D";}');

    Insert into Orders
    Values (3255,'2019-02-21 15:14:04.000','a:2:{i:3184;a:2:{i:0;s:11:"Brian Kelly";i:1;s:11:"Peter White";}i:3195;a:2:{i:0;s:11:"Brian Kelly";i:1;s:11:"Peter White";}}','a:4:{i:0;s:15:"3256-3255-3187A";i:1;s:15:"3256-3255-3187B";i:2;s:15:"3257-3255-3197A";i:3;s:15:"3257-3255-3197B";}');

    insert into Orders
    Values (3373,'2019-03-13 20:09:53.000','a:3:{i:3327;a:1:{i:0;s:12:"Andrew Jones";}i:3353;a:1:{i:0;s:12:"Andrew Jones";}i:3283;a:1:{i:0;s:12:"Andrew Jones";}}','a:3:{i:0;s:14:"3374-3373-3329";i:1;s:14:"3375-3373-3355";i:2;s:14:"3376-3373-3284";}');


    --Code to try and Split array elements

    declare @list varchar(1000)
    declare @pos int
    declare @len int
    declare @value varchar(1000)

    set @list = (Select ClassAttendees FROM Orders where OrderId = 3255) -- get list (fails for 3127)
    set @list = replace(@list,';',':') -- replace ; with : to try and standardize delimiter
    set @list = replace(@list,'"','') --remove "" around strings

    set @pos = 0
    set @len = 0

    while CHARINDEX(':',@list,@pos+1)>0
    begin
    set @len = charindex(':',@list,@pos+1) - @pos
    set @value = substring(@list,@pos,@len)
    if @len > 2
    print @value -- test output here... this will become code to insert into child table

    set @pos = charindex(':',@list,@pos+@len) +1
    end

    --End Result a separate child table of Orders
    --Showing each Class Attendee and TicketNo

    --Create child table of Orders
    CREATE TABLE [dbo].[OrderItems](
    [Id] [int] identity not null,
    [OrderId] [int] NOT NULL,
    [ClassId] [int] NOT Null,
    [ClassAttendee] [nvarchar](50) NULL,
    [ClassTicketNo] [nvarchar](50) NULL
    )

    --Insert test data into orders in format required
    INSERT INTO OrderItems
    VALUES (3125,2427,null,'2430-2429-2428');

    INSERT INTO OrderItems
    VALUES (2844,2473,'Elmer Drupp','2845-2844-2604');

    INSERT INTO OrderItems
    VALUES (3152,3083,'Shiela King','3153-3152-3085A');

    INSERT INTO OrderItems
    VALUES (3152,3083,'Kevin Smith','3153-3152-3085B');

    INSERT INTO OrderItems
    VALUES (3152,3083,'Lou Singer','3153-3152-3085C');

    INSERT INTO OrderItems
    VALUES (3255,3184,'Brian Kelly','3256-3255-3187A');

    INSERT INTO OrderItems
    VALUES (3255,3184,'Peter White','3256-3255-3187B');

    INSERT INTO OrderItems
    VALUES (3255,3195,'Brian Kelly','3257-3255-3197A');

    INSERT INTO OrderItems
    VALUES (3255,3195,'Peter White','3257-3255-3197B');

    INSERT INTO OrderItems
    VALUES (3373,3327,'Andrew Jones','3374-3373-3329');

    INSERT INTO OrderItems
    VALUES (3373,3353,'Andrew Jones','3375-3373-3355');

    INSERT INTO OrderItems
    VALUES (3373,3283,'Andrew Jones','3376-3373-3284');

    Any help splitting the array would be appreciated.

    Thank you.

     

     

     

     

     

    • This topic was modified 1 year, 2 months ago by  Slady.
    • This topic was modified 1 year, 2 months ago by  Slady.
  • Jeff Moden

    SSC Guru

    Points: 996666

    You'll probably get some responses if you make the data you posted "readily consumable".  Please see the link in my signature line below for one way to do that.

     

    Also, all the stuff that you're trying to split has braces in it... could it be from JSON?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Slady

    SSC Veteran

    Points: 218

    Hi Jeff,

    Its from a WordPress database so yes the data could be inserted into the field using json. I had investigated that option as a way to decipher the array but json data manipulation is only available from SQL Server 2016.

    I will edit my post and create a table and insert the data into it.

    Thank you for the reply.

    • This reply was modified 1 year, 2 months ago by  Slady.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply