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

IN -> Teradata_01.sql

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

OUT -> Teradata_01.sql

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

Result

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

NVP with optional parameters ignored

Teradata

Query

IN -> Teradata_02.sql

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

OUT -> Teradata_02.sql

SELECT
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 1),
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 2),
    PUBLIC.NVP_UDF('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=', 1);

Result

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

NVP with spaces in delimiters

Teradata

Query

IN -> Teradata_03.sql

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

OUT -> Teradata_03.sql

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

Result

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

NVP with non-literal delimiters

Teradata

Query

IN -> Teradata_04.sql

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

Snowflake

Query

OUT -> Teradata_04.sql

SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', valueDelimiter, 2) /*** SSC-FDM-TD0008 - WHEN NVP_UDF FOURTH PARAMETER IS NON-LITERAL AND IT CONTAINS A BACKSLASH, THAT BACKSLASH NEEDS TO BE ESCAPED ***/;

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. SSC-FDM-TD0008: Non-literal delimiters with spaces need their backslash scaped in snowflake.