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