The behavior of GETPART when there is nothing in the original string after the delimiter is currently inconsistent between the ansi/sqlite implementation versus what other dialects to akin to the Snowflake SPLIT_PART function (which GETPART is based on)
The current implementation of GETPART in the ansi/sqlite translation compared to GETPART in MySQL and Snowflake is as follow:
| GETPART |
ANSI/SQLITE |
MySQL/SnowFlake |
| GETPART("string", "string", 1) -> |
"" |
"" |
| GETPART("string", "string", 2) -> |
None |
"" |
| GETPART("string", "string", 3) -> |
None |
None |
| GETPART("hello.world.", ".", 1) -> |
"hello" |
"hello" |
| GETPART("hello.world.", ".", 2) -> |
"world" |
"world" |
| GETPART("hello.world.", ".", 3) -> |
None |
"" |
| GETPART("hello.world.", ".", -1) -> |
None |
"" |
| GETPART("hello.world.", ".", -2) -> |
"world" |
"world" |
| GETPART("hello.world.", ".", -3) -> |
"hello" |
"hello" |
| GETPART("abab", "ab", 1) -> |
"" |
"" |
| GETPART("abab", "ab", 2) -> |
"" |
"" |
| GETPART("abab", "ab", 3) -> |
None |
"" |
| GETPART("abab", "ab", -1) -> |
None |
"" |
| GETPART("abab", "ab", -2) -> |
"" |
"" |
| GETPART("abab", "ab", -3) -> |
"" |
"" |
The desire behavior for the ansi/sqlite GETPART to maintain consistency through the dialects is the behavior of the MySQL/Snowflake implementation.
The behavior of
GETPARTwhen there is nothing in the original string after the delimiter is currently inconsistent between the ansi/sqlite implementation versus what other dialects to akin to the SnowflakeSPLIT_PARTfunction (whichGETPARTis based on)The current implementation of
GETPARTin the ansi/sqlite translation compared toGETPARTin MySQL and Snowflake is as follow:The desire behavior for the ansi/sqlite
GETPARTto maintain consistency through the dialects is the behavior of the MySQL/Snowflake implementation.