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

I have two columns as follows:

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:

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!

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]

Note, that a problem here are possible special characters in the stored text. Starting from SQL Server 2017, you may escape these special characters with STRING_ESCAPE().

...
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(REPAY_AMOUNT, 'json'), N'', '","'), '"]')) RA
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(REPAY_REF, 'json'), N'', '","'), '"]')) RR 
...