Do we have mysql equivalent FIND_IN_SET in singlestore?

Hi Team,

Do we have mysql equivalent FIND_IN_SET function in Singlestore? if please let me know.

DIM_RULE_CON

ID Rule_id
01 A01,A02,A03
02 A04
03 A05,A06,A07

FACT_FIN
TIME RULE_ID AMOUNT
012021 A01 1000
012021 A02 2000
012021 A03 3000
012021 A04 1000
022021 A05 2000
032021 A06 3000

SELECT DISTINCT TIME FROM FACT_FIN F
JOIN DIM_RULE_CON C ON FIND_IN_SET (F.RULE_ID,C.RULE_ID)
WHERE F.AMOUNT>1000;

– THIS QUERY IS WORKING FINE IN MYSQL , when am join with comma separated field to Single value field.

But i could not find solutions or equivalent function in singlestore.

Kindly help me

We don’t, but you could code an MPSQL scalar function to do that yourself.

Follow the specification set out here:

You could take the 2nd argument, put brackets around it, then call json_to_array, then loop through the array looking for what you want. There are other ways to do it too, I’m sure.

Better yet, use SPLIT to help build FIND_IN_SET as a UDF.

Okay thanks will try

Any luck with Find_in_set function?
We’re facing same issue.
@hanson, what would be the best approach for this. Not sure how the split function helps.

You could write a udf like

my_find_in_set(k, l)

that splits comma-separated list (string) l to an array, then loops through it, looking for l.

This approach might also help:

singlestore> select json_array_contains_string('["a","b","c"]', "b");
+--------------------------------------------------+
| json_array_contains_string('["a","b","c"]', "b") |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.19 sec)

singlestore> select json_array_contains_string('["a","b","c"]', "q");
+--------------------------------------------------+
| json_array_contains_string('["a","b","c"]', "q") |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+

Depending on the situation, you might also want to turn the list into a rowset of its items, then use SQL selects, joins or EXISTS to test for membership. E.g.:

singlestore> select * from table(split("a,b,c", ",")) where table_col = "c";
+-----------+
| table_col |
+-----------+
| c         |
+-----------+
1 row in set (0.02 sec)

singlestore> select * from table(split("a,b,c", ",")) where table_col = "q";
Empty set (0.00 sec)