STRING_SPLIT and CROSS APPLY

Help Procedural SQL
1

I’m looking for a way to do this in SingleStore:
I have a column with coma separated string that I need to split to different rows and the join to the table as a new column

sql server script:

        SELECT    id,
            	     product_ids,
                         value new_col
        FROM platform.manual_rule
       CROSS APPLY STRING_SPLIT(product_ids, ',')

image

2

Thank you for your question and welcome to the SingleStore forums.

There is a SPLIT() function and a UDF example for string_split():

We’ll check the CROSS APPLY to see if an equivalent is possible.

3

You can accomplish this with the following query:

SELECT   id,
         product_ids,
         value new_col
FROM platform.manual_rule
JOIN TABLE(SPLIT(product_ids, ','))
4

thank you,
I tried but I got this error massage (I also try with string split()) :
ERROR 1054 ER_BAD_FIELD_ERROR: Unknown column ‘value’ in ‘field list’

5

I had a slight syntax error, this should work

SELECT   id,
         product_ids,
         table_col new_col
FROM platform.manual_rule
JOIN TABLE(SPLIT(product_ids, ','))
2 Likes
6

thank you so much! it helps me a lot

2 Likes
7

We also now support explicit LATERAL JOIN syntax that works for this and other kinds of queries. E.g.

singlestore> select * from r;
+------+------+
| id   | s    |
+------+------+
|    2 | c,d  |
|    1 | a,b  |
+------+------+
2 rows in set (0.03 sec)

singlestore> select * from r lateral join table(split(s,","));
+------+------+-----------+
| id   | s    | table_col |
+------+------+-----------+
|    2 | c,d  | c         |
|    2 | c,d  | d         |
|    1 | a,b  | a         |
|    1 | a,b  | b         |
+------+------+-----------+

You can also use a correlated subquery on the right of LATERAL. We still do an implicit lateral join when you used the TABLE function after a comma in the FROM list.

8

Correction: when you use JOIN TABLE(…) there is an implicit lateral operation. Explicit use of LATERAL with joins to TABLE is not allowed. E.g. this works:

create table r(id int, s text);
insert r values (1,"c,d"),(2,"a,b");

select * from r join table(split(s,","));

id,s,table_col
2,"a,b",a
2,"a,b",b
1,"c,d",c
1,"c,d",d

And, now we do support LATERAL join for other kinds of queries, as is fully described here: