Do we have mysql equivalent FIND_IN_SET in singlestore?

Help Procedural SQL
1

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

2

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.

3

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

4

Okay thanks will try

5

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.

6

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)