STRING_SPLIT -Query is adding more lines rather then allocating serial number in sequence

Question

I am trying to split Serial No of a order item table , which has string separated values. I am able to separate the value but serial no allocation is not happening correctly, Its just showing additional rows.

Table has data like

OrderID OrderItemID Item    Price   SerialNo
100     101         P1      200.50  OW52288-OW52289-OW52290-OW52291-OW52292-OW52293
100     102         P1      200.50  NULL
100     103         P1      100.50  NULL
100     104         P1      300.40  NULL
100     105         P1      600.30  NULL
100     106         P1      300.50  NULL
100     107         P1      500.70  NULL
100     108         P1      200.60  NULL
100 109 P1  800.60  NULL

Result coming

OrderID OrderItemID Item    Price   value
100     101         P1     200.50   OW52288
100     101         P1     200.50   OW52289
100     101         P1     200.50   OW52290
100     101         P1     200.50   OW52291
100     101         P1     200.50   OW52292
100     101         P1     200.50   OW52293

Required result

OrderID OrderItemID Item    Price   SerialNo
100     101         P1      200.5   OW52288
100     102         P1      200.5   OW52289
100     103         P1      100.5   OW52290
100     104         P1      300.4   OW52291
100     105         P1      600.3   OW52292
100     106         P1      300.5   OW52293
100     107         P1      500.7   NULL
100     108         P1      200.6   NULL
100     109         P1      800.6   NULL

--Can please help me

Table & query I am writing

CREATE TABLE dbo.TestOrderItemSerial(
    [OrderID] [int] NOT NULL,
    [OrderItemID] [int] NOT NULL,
    [Item] [nvarchar](50) NULL,
    [Price] [money] NOT NULL,
    [SerialNo] [nvarchar](100) )

Insert into dbo.TestOrderItemSerial values
(100,101,'P1',200.50,'OW52288-OW52289-OW52290-OW52291-OW52292-OW52293')
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price)values
(100,102,'P1',200.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,103,'P1',100.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,104,'P1',300.40)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,105,'P1',600.30)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,106,'P1',300.50)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,107,'P1',500.70)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,108,'P1',200.60)
Insert into dbo.TestOrderItemSerial (OrderID,OrderItemID,Item,Price) values
(100,109,'P1',800.60)

    

select OrderID,OrderItemID,Item,Price,value 
            FROM dbo.TestOrderItemSerial  with (nolock) 
    CROSS APPLY String_Split(REPLACE(REPLACE((
    CASE WHEN  CHARINDEX('-',SerialNo) = 3 THEN REPLACE (SerialNo,'-','') 
         WHEN  CHARINDEX(' ',SerialNo) = 3 THEN REPLACE (SerialNo,' ','') 
         WHEN  CHARINDEX(' ',SerialNo) = 6 THEN REPLACE (SerialNo,' ','-') 
         ELSE SerialNo END)
        ,',','-'),'/','-'),'-')

Data can be like this also

What version of SQL Server? Is it always only a single row per OrderId with a serial number? — Stu, Mar 04 '23 at 21:46
Please don't post code or data in comments, they are completely unreadable. — Aaron Bertrand, Mar 04 '23 at 21:53
By what logic are you applying the split data to other rows? Remember SQL tables have no default ordering unless you specify — Charlieface, Mar 04 '23 at 21:58
Can you provide fully representative sample data in the question, if there can be multiple rows with a serial number then show it and explain the expected outcome. — Stu, Mar 04 '23 at 21:59
I have just done. Showed the required result and 2 way the data can be. — S B, Mar 04 '23 at 22:03
try with OUTER APPLY - if this returns more rows than you need then consider adding a where clause — Paul Maxwell, Mar 04 '23 at 22:03
There will be more order lines then total number of serial no. But if this case occurs then first five only should get allocated. — S B, Mar 04 '23 at 22:13
Are those serial numbers blocks always going to be consecutive? — shawnt00, Mar 04 '23 at 22:15

score 1 · Accepted Answer · answered Mar 04 '23 at 22:17

Probably a few ways you can approach this, none particularly pretty as dealing with delimited strings just isn't nice.

You need to split the string into ordered rows that can join to your existing table.

If you were using SQL Server 2022 (16) then string_split would be fine, however for SQL Server 2019 it is not always guaranteed rows will be returned in order, so other methods like using a json array can be used.

Try the following which splits the string(s) and assigns a sequential number on which to join:

with s as (
  select t.OrderId, j.SerialNo, 
    Row_Number() over(partition by Orderid order by t.OrderItemID, j.Seq) seq
  from TestOrderItemSerial t
  cross apply (
    select j.[value] SerialNo, 1 + Convert(tinyint, j.[key]) Seq 
    from OpenJson(Concat('["',replace(Serialno, '-', '","'),'"]')) j
  )j
  where t.SerialNo is not null
), t as (
  select *, Row_Number() over(partition by OrderId order by OrderItemId)Seq
  from TestOrderItemSerial t
)
select t.OrderId, t.OrderItemId, t.Item, t.Price, s.Serialno
from t
Left join s on s.OrderId = t.OrderId and s.Seq = t.Seq
order by OrderId, t.Seq;

Here is a demo fiddle

Aaron Bertrand · Answer 2 · 2023-03-05T00:37:32.627

I like using a function for this kind of thing when I can't fix the data (which is the right thing to do). Assuming SQL Server 2016 or better (always good to tell us via a tag on the question, like sql-server-2019):

CREATE OR ALTER FUNCTION dbo.SplitOrdered_JSON
(
  @List      varchar(max),
  @Delimiter varchar(10)
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT seq = [key] + 1, value 
    FROM OPENJSON(N'["' + REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    @Delimiter, '","') + '"]') AS x
  );

Now the query is:

WITH sn AS
(
  SELECT t.OrderID, j.seq, j.value
  FROM 
  (
    SELECT t.OrderID, sn = STRING_AGG(t.SerialNo, '-')
      WITHIN GROUP (ORDER BY t.OrderItemID)
    FROM dbo.TestOrderItemSerial AS t
    WHERE t.SerialNo > '' GROUP BY t.OrderID
  ) AS t CROSS APPLY dbo.SplitOrdered_JSON(t.sn, '-') AS j
),
Orders AS
(
  SELECT OrderID, OrderItemID, Item, Price,
    rn = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderItemID)
  FROM dbo.TestOrderItemSerial
)
SELECT o.OrderID, o.OrderItemID, o.Item, o.Price, sn.value
  FROM Orders AS o 
  LEFT OUTER JOIN sn
  ON o.OrderID = sn.OrderID
  AND o.rn = sn.seq
ORDER BY o.OrderID, o.OrderItemID;

Example db<>fiddle demonstrating both cases in the question

With the new information, sure, we can handle any number of delimiters. Slash, space, dash, pencil... you name it. We can just use TRANSLATE() to convert them all to dashes first:

WITH sn AS
(
  SELECT t.OrderID, j.seq, j.value
  FROM 
  (
    SELECT t.OrderID, sn = STRING_AGG(
      TRANSLATE(t.SerialNo, N'/ ✎',N'---'), N'-')
------^^^^^^^^^--------------- only change
      WITHIN GROUP (ORDER BY t.OrderItemID)
    FROM dbo.TestOrderItemSerial AS t
    WHERE t.SerialNo > '' GROUP BY t.OrderID
  ) 
  AS t CROSS APPLY dbo.SplitOrdered_JSON(t.sn, '-') AS j
),
...

Updated db<>fiddle example

But please, please, please consider storing your data properly in the first place. Seems whoever designed this system forgot about the R in RDBMS - if these serial numbers are independent pieces of data, they should always be stored separately and never shmeared together like goop.

And please state all of your requirements up front. When you post half of the info, people who are volunteering their valuable time might only help you solve half of the problem, but they waste potentially all of their time. See chameleon questions and How do I ask a good database question?

How can I use the above code multiple separator, its not necessary the separator will be - or it can be space or / also in a same order. — S B, Mar 04 '23 at 23:36
Just few cases order is there with different identifier. Apology , I should have mentioned earlier. — S B, Mar 05 '23 at 00:10
@SB I suggest you create a *new* question now if you have new information — Charlieface, Mar 05 '23 at 00:15
@AaronBertrand, Thanks for helping me in this. Will take care in future. — S B, Mar 05 '23 at 00:57

STRING_SPLIT -Query is adding more lines rather then allocating serial number in sequence

2 Answers2