Cross Apply used Sub Query not Return Result

Question

I want to extract version data from the deviceinfo column. For that I am using Cross Apply.

Order
----------------------------------
OrdeId       DeviceInfo                
----------------------------------    
10      PageSize|BGColor|3000|V1.0
11      PageSize|BGColor|3000|V2.0
12      PageSize|BGColor|3000|V3.0
----------------------------------

I have used the below query

Select * From 
(
    Select OrderId, DeviceInfo, Value, ROW_NUMBER() Over(Partition By DeviceInfo Order By OrderId Asc) As Rn
    FROM Order As Ord
    Cross Apply STRING_SPLIT(DeviceInfo, '|') As Di
    Where IsNull(Ord.DeviceInfo,'') != '' And OrderId='10'
)A Where A.Rn=4 And A.OrderId='10'

I got the Result.

OrderId       DeviceInfo              Value  Rn
------------------------------------------------
10      PageSize|BGColor|3000|V1.0     V1.0  4

If I use the query without orderid filter in sub query I got an Empty result.

Select * From 
(
    Select OrderId, DeviceInfo, Value, ROW_NUMBER() Over(Partition By DeviceInfo Order By OrderId Asc) As Rn
    FROM Order As Ord
    Cross Apply STRING_SPLIT(DeviceInfo, '|') As Di
    Where IsNull(Ord.DeviceInfo,'') != ''
)A Where A.Rn=4 And A.OrderId='10'

Kindly help me to fix this issue

Answer 1

A possible explanation for the unexpected results is the combination of the following issues:

• The wrong use of PARTITION BY and ORDER BY clauses in the ROW_NUMBER() function call. ROW_NUMBER() Over (Partition By DeviceInfo Order By OrderId Asc) numbers rows in random and unexpected order.
• Using STRING_SPLIT() to extract the substring by position is a tricky apporach. As is mentioned in the documentation, the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.

In your case (if you want to extract the substring by position) you may try a JSON-based approach to parse the DeviceInfo column. You need to transform the data into a valid JSON array (PageSize|BGColor|3000|V1.0 into ["PageSize","BGColor","3000","V1.0"]) and parse this array with OPENJSON(). The result from the OPENJSON() call is a table with columns key, value and type and the key column holds the 0-based index of the element in the specified array.

Table:

CREATE TABLE [Order] (
   OrderId int,
   DeviceInfo varchar(1000)               
)
INSERT INTO [Order] (OrderId, DeviceInfo)
VALUES
   (10, 'PageSize|BGColor|3000|V1.0'),
   (11, 'PageSize|BGColor|3000|V2.0'),
   (12, 'PageSize|BGColor|3000|V3.0')

Statement:

SELECT OrderId, DeviceInfo, [Value]
FROM [Order] AS o
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(o.DeviceInfo, '|', '","'), '"]')) AS j
WHERE o.OrderId = 10 AND j.[key] = '3'

Result:

OrderId DeviceInfo                 Value
----------------------------------------
10      PageSize|BGColor|3000|V1.0 V1.0