fnMFSplitPairedStrings¶

Signature¶

dbo.fnMFSplitPairedStrings(
    @PairColumn1             VARCHAR(MAX),
    @PairColumn2             VARCHAR(MAX),
    @Delimiter               CHAR(1),
    @Delimiter_MultiLookup   CHAR(1)
)

Returns¶

Table-valued result expanding pairs, with support for multi-lookup values: - Pair1 NVARCHAR(…) - Pair2 NVARCHAR(…) - Position INT (1-based index) - SubIndex INT (1-based index within multi-lookup expansion)

Parameters¶

@PairColumn1 VARCHAR(MAX) (required) - First delimited sequence, e.g., ‘1,2,3’.
@PairColumn2 VARCHAR(MAX) (required) - Second delimited sequence, e.g., ‘a,b,c’ or ‘x#y#z’ when multi-lookup values are nested.
@Delimiter CHAR(1) (required) - Primary delimiter for pairing, e.g., ‘,’.
@Delimiter_MultiLookup CHAR(1) (required) - Secondary delimiter used to split multi-lookup values (e.g., ‘#’).

Purpose¶

Convert two delimited sequences into paired rows and expand multi-lookup values within the second sequence using a secondary delimiter.

Examples¶

-- Simple pairing
SELECT *
FROM dbo.fnMFSplitPairedStrings('1,2,3', 'a,b,c', ',', '#');

-- Pairing with multi-lookup values in the second sequence
SELECT *
FROM dbo.fnMFSplitPairedStrings('10,20', 'x#y#z,alpha#beta', ',', '#');

Changelog¶

Date	Author	Description
2019-08-30	JC	Added documentation
2017-12-21	LC	Change name of function. Allow for including multilookup value with multiDelimiter, change names of parameters
2014-09-13	AC	Initial Version - QA