How to split multi-values from two different columns and display split value from one column against split value of another column?

Question

I have two columns as follows:

Repay_Aount	Repay_Ref
150.063829.07	T21Q1P-20210529T21XYN-20210428
160.1216502429.49	T21YMG-20210628T21GVX-20210531T21Q1P-20210529
115.9104.2826001461.47	T21JK9-20210731T21JG1-20210731T21QZP-20210724T21YMG-20210628

Repay_Amount has some amounts separated by a unicode character .

The Repay_Ref too has some values separated by  but, if you notice, every multi-value in each cell ends with a date.

I want to display just the date from Repay_Ref against the corresponding Repay_Amount amount.

What I want to achieve is:

Repay_Aount	Repay_Ref
150.06	20210529
3829.07	20210428
160.12	20210628
1650	20210531
2429.49	20210529
115.9	20210731
104.28	20210731
2600	20210724
1461.47	20210628

I tried the following query but couldn't get the desired results. There was duplication.

SELECT      REPAY_AMOUNT,
            RA.Value AS [SPLIT_REPAY_AMOUNT],
            RR.Value AS [SPLIT_REPAY_ref],
            REPAY_ref
FROM        InsightImport.dbo.AA_BILL_DETAILS bil
CROSS APPLY STRING_SPLIT(REPAY_AMOUNT, N'') RA
CROSS APPLY STRING_SPLIT(REPAY_ref, N'') RR

Any help shall be appreciated!

Answer 1

You need a splitter function, that returns the ordinal position of each substring. Starting from SQL Server 2022 STRING_SPLIT() supports an optional enable_ordinal parameter.

For earlier versions a JSON-based approach is an option. The idea is to transform the stored text into a valid JSON array (115.9104.2826001461.47 into ["115.9","104.28","2600","1461.47"]) and parse this array with OPENJSON() and default schema. The result is a table with columns key, value, type and the key column holds the index of the element in the specified array.

SQL Server 2022:

SELECT RA.[value] AS [SPLIT_REPAY_AMOUNT], RR.[value] AS [SPLIT_REPAY_ref]
FROM AA_BILL_DETAILS
CROSS APPLY STRING_SPLIT(REPAY_AMOUNT, N'', 1) RA
CROSS APPLY STRING_SPLIT(REPAY_ref, N'', 1) RR
WHERE RA.[ordinal] = RR.[ordinal]

SQL Server 2016+:

SELECT RA.[value] AS [SPLIT_REPAY_AMOUNT], RR.[value] AS [SPLIT_REPAY_ref]
FROM AA_BILL_DETAILS
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_AMOUNT, N'', '","'), '"]')) RA
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(REPAY_REF, N'', '","'), '"]')) RR
WHERE RA.[key] = RR.[key]