CODEWITHSUNDEEP
sqlsql-serversplitdelimitermultivalue
K H A N
K H A N

Reputation: 232

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_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!

Upvotes: 3

Views: 219

Answers (1)

Zhorov
Zhorov

Reputation: 29993

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 
...

Upvotes: 3

Related Questions