How to conditionally parse a default value in a string when missing or not

Question

I would like to conditionally determine whether I have a default value in a string or not (the zeros in my 3rd value in my mapping string below).

I have the following string:

'PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|0'

The above represents SourceColumn|TargetColumn|DefaultValue separated by a semicolon. Thus, I have two delimiters in my string--a pipe and a semicolon.

I am separating my source, target, and default columns and it's working well in my code below:

DECLARE @Mapping NVARCHAR(max) = 'PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|0'
DECLARE @SourceTableColumns VARCHAR(256)
DECLARE @TargetTableColumns VARCHAR(256)
DECLARE @DefaultTableColumn VARCHAR(256)

    SELECT   @SourceTableColumns = isnull(@SourceTableColumns + ',', '') + SourceTableColumn
            ,@TargetTableColumns = isnull(@TargetTableColumns + ',', '') + TargetTableColumn
            ,@DefaultTableColumn = isnull(@DefaultTableColumn + ',', '') + DefaultTableColumn
    FROM (
        SELECT   parsename(replace(value, '|', '.'), 3) AS SourceTableColumn
                ,parsename(replace(value, '|', '.'), 2) AS TargetTableColumn
                ,parsename(replace(value, '|', '.'), 1) AS DefaultTableColumn
        FROM (
            SELECT *
            FROM String_split(@Mapping,';')
            ) a
        WHERE value <> ' '
        ) a

print 'Mapping: ' + @Mapping
print 'Source: ' + @SourceTableColumns
print 'Target: ' + @TargetTableColumns
print 'Default: ' + @DefaultTableColumn

Here are my results when my 3rd value for defaults is always populated (the zeros in my 3rd value in my mapping string):

PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|0
Source: PriceSrc,CurrencySrc,ProductSrc
Target: PriceTrg,CurrencyTrg,ProductTrg
Default: 0,0,0

However, when I even have one missing default value, such as below (note that I've excluded one of the zeros at the end of my string)...

'PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|'

My entire logic breaks down and it blanks out all of my variables. It gives me the below result (only my mapping):

Mapping: PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|

Please note that the function of the default value 3rd parameter is to serve as an override value for users to enter any string or numeric value if they choose to. I do not want to always force a value in my default if it's NULL as it's not always required.

How do I make my default value (i.e., DefaultTableColumn) in my mapping string conditional? As mentioned, I may not always have a default value, but I still would like to keep my @SourceTableColumns and @TargetTableColumns variables intact.

---

Modified logic as proposed by Zohar Peled:

SELECT   @SourceTableColumns = isnull(@SourceTableColumns + ',', '') + SourceTableColumn
        ,@TargetTableColumns = isnull(@TargetTableColumns + ',', '') + TargetTableColumn
        ,@DefaultTableColumn = isnull(@DefaultTableColumn + ',', '') + DefaultTableColumn
FROM (

SELECT  
        JSON_VALUE(JsonMapping, '$.V[0]') As SourceTableColumn,
        JSON_VALUE(JsonMapping, '$.V[1]') As TargetTableColumn,
        NULLIF(JSON_VALUE(JsonMapping, '$.V[2]'), '') As DefaultTableColumn
    FROM String_split(@Mapping, ';')
    CROSS APPLY
    (
        SELECT '{"V":["' + REPLACE([value], '|', '", "') +'"]}' As JsonMapping
    ) JsonData

    ) a

print 'Mapping: ' + @Mapping
print 'Source: ' + @SourceTableColumns
print 'Target: ' + @TargetTableColumns
print 'Default: ' + @DefaultTableColumn

Here's what I am getting as results:

Mapping: PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|
Source: PriceSrc,CurrencySrc,ProductSrc
Target: PriceTrg,CurrencyTrg,ProductTrg

I am not getting correct results as I am getting all NULLs for my DefaultTableColumn.

The expected value for @DefaultTableColumn would be 0,0,

Regards, Shawn

Answer 1

First, since 2016 version, SQL Server has a built in string_split function which out-performs any user-defined function.

Second, I would probably use replace to generate a json array from the string, and then json_value to get the values:

DECLARE @Mapping NVARCHAR(max) = 'PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|'


SELECT  JSON_VALUE(JsonMapping, '$.V[0]') As SourceColumn,
        JSON_VALUE(JsonMapping, '$.V[1]') As TargetColumn,
        -- json_value returns an empty string if no value found, nullif converts it to a null
        NULLIF(JSON_VALUE(JsonMapping, '$.V[2]'), '') As DefaultValue
FROM string_split(@Mapping, ';')
CROSS APPLY
(
    SELECT '{"V":["' + REPLACE([value], '|', '", "') +'"]}' As JsonMapping
) As JsonData

Results: (Note that the last default value is missing)

SourceColumn    TargetColumn    DefaultValue
PriceSrc        PriceTrg        0
CurrencySrc     CurrencyTrg     0
ProductSrc      ProductTrg      NULL

Update
To get the results into variables you can use another built in function (since 2017 version) called string_agg:

DECLARE @Mapping NVARCHAR(max) = 'PriceSrc|PriceTrg|0;CurrencySrc|CurrencyTrg|0;ProductSrc|ProductTrg|',
        @SourceTableColumns NVARCHAR(max),
        @TargetTableColumns NVARCHAR(max),
        @DefaultTableColumn NVARCHAR(max)

SELECT   @SourceTableColumns = STRING_AGG(JSON_VALUE(JsonMapping, '$.V[0]'), ',')
        ,@TargetTableColumns = STRING_AGG(JSON_VALUE(JsonMapping, '$.V[1]'), ',')
        ,@DefaultTableColumn = STRING_AGG(NULLIF(JSON_VALUE(JsonMapping, '$.V[2]'), ''), ',')
FROM String_split(@Mapping, ';')
CROSS APPLY
(
    SELECT '{"V":["' + REPLACE([value], '|', '", "') +'"]}' As JsonMapping
) As JsonData

print 'Mapping: ' + @Mapping
print 'Source: ' + @SourceTableColumns
print 'Target: ' + @TargetTableColumns
print 'Default: ' + @DefaultTableColumn

You can see a live demo on db<>fiddle