I am trying to convert some MySQL into Snowflake SQL but I cannot get the same results in Snowflake. I believe the issue is with find_in_set. I found that contains is a similar function but it doesn't seem to be working

Does anyone know a suitable replacement for find_in_set in Snowflake?

Here are the differences I've found between contains and find_in_set:

contains: Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.

find_in_set: The FIND_IN_SET() function returns the position of a string within a list of strings.

CodePudding user response：

Perhaps you are looking for the POSITION function, here is the documentation for it. https://docs.snowflake.com/en/sql-reference/functions/position.html

It states that it "Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument."

This sounds like what you need.

CodePudding user response：

MySQL FIND_IN_SET appear to be working over an strlist, which looks to be an array of strings.

Thus in the example:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

so is not the string position of the substring b, but the array position.

So using ARRAY_CONSTRUCT just to make the array, and ARRAY_POSITION to find the zero based offset (mysql is 1 offset with 0 meaning not present) and Snowflake returns NULL in not preset

SELECT array_construct('a','b','c','d') as strlist
    ,array_position('a'::variant, strlist) as a_pos
    ,array_position('b'::variant, strlist) as b_pos
    ,array_position('k'::variant, strlist) as k_pos;

gives:

You can also use SPLIT if your string list is a single string like:

split('a,b,c,d',',') as strlist