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():

https://docs.singlestore.com/db/v7.8/en/reference/sql-reference/string-functions/split.html

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