Split/explode mysql string in numeric values

Question

I've created a (complex) sub-query where I put the results in a variable like:

@mylist := (select .... )

The returned value is a comma-delimeted string.. Because in the sub-query I also use concat(), the value is a string.

In the same SQL I want to use this variable in another subquery like:

where table.mycolumn IN (@mylist)

The problem is that because @mylist is a string Mysql reads the query as:

where table.mycolumn IN('575030,655156,655157')

while I want it to be execute as

where table.mycolumn IN(575030,655156,655157)

How can I convert the string to an (numeric) array?

PS: I am using mysql 5.1

Cavaz · Accepted Answer · 2017-08-31 17:21:32Z

3

You can use the function FIND_IN_SET (it is available in the mysql version you specified). It splits the provided string (in your case stored in the variable) using the comma as separator and returns the index of the index of the first occurrence of the specified value (0 if not found)

/* building the list */
> SELECT @mylist :=GROUP_CONCAT(id SEPARATOR ',') FROM users WHERE id < 10;
+-----------------------------------------+
| @mylist:=group_concat(id separator ',') |
+-----------------------------------------+
| 0,2,3,4,5,6,7,8,9                       |
+-----------------------------------------+

> SELECT id, mail FROM users WHERE FIND_IN_SET(id, @mylist);

The automatic casting is enough to automatically manage the comparison between the original type and the final string most of the cases.

UPDATE

Although it answers your question, the proposed solution can get slow when looking for huge amounts of ids. A way better solution would be to drop the use of variables and store the results in a temporary table

CREATE [TEMPORARY] TABLE IF NOT EXISTS tmp_users (
  "id" INT,
  PRIMARY KEY (id)
)

INSERT INTO tmp_users (...);

edited Aug 31, 2017 at 17:21

answered Oct 12, 2016 at 9:34

Cavaz

3,13927 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

MontyPython Over a year ago

Will this work irrespective of what the separator in @mylist is?

Cavaz Over a year ago

no, according to the documentation there is no parameter to change the separator. It is possible to add a FIND_IN_SET(id, REPLACE(@mylist, 'my_separator', ',')) though, but it won't work if the values to extract can contain commas.

MontyPython Over a year ago

Given this limitation FIND_IN_SET is good to have. I don't remember how this is done in Oracle 11g.

Patrick Over a year ago

Just one more question; is it better to use WHERE FIND_IN_SET(id, @mylist)>0 , because if no matches are found it returns '0' ?

Cavaz Over a year ago

0 evaluates to false (FALSE in fact is 0), so since there is no way the function returns a negative value the >0 is superfluous.

|

Collectives™ on Stack Overflow

Split/explode mysql string in numeric values

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related