CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Shah
Shah

Reputation: 5018

mysql string split

I am using the following queries and getting diff results.

SELECT SUM(amt) FROM table1 WHERE id in (114,116) and another_id = 10;

result is 10+20 = 30

SELECT SUM(amt) FROM table1 WHERE id in (REPLACE("116,114",'"',"")) and another_id = 10;

result is 10

i was suggested to use a string split function and put the splits in a table,can someone point me to an example?

Upvotes: 0

Views: 374

Answers (2)

symcbean
symcbean

Reputation: 48387

SELECT SUM(amt) FROM table1 WHERE id in (REPLACE("116,114",'"',"")) and another_id = 10;

It would help if we knew what you were trying to do.

At a guess, I suspect that you want to provide a facility where someone can specify a set of ids and pass that as an argument to the query.

A simple solution would be:

WHERE CONCAT('%,', id, ',%') LIKE ('${submitted_string}')

But this will not be able to use any index on table1.id

AFAIK there's no way to cast an array/string to a table type in MySQL, so you'd need to parse the string and put the values temporaril;y into a table along with a key referencing the session (or into a temporary table). Doing this in SQL is rather hard work - it's a lot simpler to use a logic tier on top - which, based on my interpretation of what your trying to achieve you must already have.

Upvotes: 1

Sparky
Sparky

Reputation: 15105

Based on your code, it appears ID is a numeric field...

Try this for the second example...

SELECT SUM(amt) FROM table1 WHERE ","+CAST(id AS CHAR)+"," in (","+"116,114"+",") 
                             and another_id = 10;

Upvotes: 0

Related Questions