April 23, 2019 at 11:05 pm
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.
April 24, 2019 at 2:21 pm
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
Change is inevitable... Change for the better is not.
April 24, 2019 at 5:04 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply