NVP

Translation specification for transforming the NVP function to an equivalent UDF in Snowflake

Description

Extracts the value of the key-value pair where the key matches the nth occurrence of the specified name to search. See NVP.

[TD_SYSFNLIB.] NVP (
in_string,
name_to_search
[, name_delimiters ]
[, value_delimiters ]
[, occurrence ]
)

Sample Source Patterns

NVP basic case

Teradata

Query

SELECT
NVP('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1),
NVP('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2),
NVP('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2);

Result

COLUMN1        | COLUMN2 | COLUMN3 |
---------------+---------+---------+
orange chicken | world!  | Susana  |

Snowflake

Query

SELECT
PUBLIC.NVP_UDF('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/,
PUBLIC.NVP_UDF('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/,
PUBLIC.NVP_UDF('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/;

Result

COLUMN1        | COLUMN2 | COLUMN3 |
---------------+---------+---------+
orange chicken | world!  | Susana  |

NVP with optional parameters ignored

Teradata

Query

SELECT
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color'),
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', 2),
NVP('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=');

Result

COLUMN1 | COLUMN2 | COLUMN3     |
--------+---------+-------------+
Green   | Blue    | Los Angeles |

Snowflake

Query

SELECT
PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 1) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/,
PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/,
PUBLIC.NVP_UDF('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=', 1) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/;

Result

COLUMN1 | COLUMN2 | COLUMN3     |
--------+---------+-------------+
Green   | Blue    | Los Angeles |

NVP with spaces in delimiters

Teradata

Query

SELECT
NVP('store = whole foods&&store: ?Bristol farms','store', '&&', '\ =\  :\ ?', 2),
NVP('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\ =\ ', 2);

Result

COLUMN1       | COLUMN2 |
--------------+---------+
Bristol farms | world!  |

Snowflake

Query

SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms','store', '&&', '\\ =\\  :\\ ?', 2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/,
PUBLIC.NVP_UDF('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\\ =\\ ', 2) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/;

Result

COLUMN1       | COLUMN2 |
--------------+---------+
Bristol farms | world!  |

NVP with non-literal delimiters

Teradata

Query

SELECT NVP('store = whole foods&&store: ?Bristol farms','store', '&&', valueDelimiter, 2);

Snowflake

Query

SELECT 
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', valueDelimiter, 2) /*** MSC-WARNING - MSCEWI2080 - NON-LITERAL DELIMITERS WITH SPACES NEED THEIR BACKSLASH SCAPED IN SNOWFLAKE ***/ /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'NVP_UDF' INSERTED. ***/;

Known Issues

1. Delimiters with spaces (\ ) need to have the backslash scaped in Snowflake

In Teradata, delimiters including space specify them using "\ " (see NVP with spaces in delimiters), as shown in the examples, in Teradata it is not necessary to escape the backslash, however, it is necessary in Snowflake. Escaping the backslashes in the delimiter can be done automatically by SnowConvert but only if the delimiter values are literal strings, otherwise the user will be warned that the backlashes could not be escaped and that it may cause different results in Snowflake.

Related EWIs

1. MSCEWI1020: Custom UDF inserted.

2. MSCEWI2080: Non-literal delimiters with spaces need their backslash scaped in snowflake.