Please take a look at it:
SELECT calculate_both_score_and_reputation(u.id) AS sr FROM users u
It returns something like this:
+----------+
| sr |
+----------+
| 0,1322 |
| 3, 232 |
| -2, 9978 |
| 31, 5745 |
+----------+
Now I want to split the result of that function into two different columns:
SELECT SUBSTRING_INDEX(calculate_both_score_and_reputation(u.id),',', 1) AS s,
SUBSTRING_INDEX(calculate_both_score_and_reputation(u.id),',', -1) AS r,
FROM users u
Here is the result:
+----+------+
| s | r |
+----+------+
| 0 | 1322 |
| 3 | 232 |
| -2 | 9978 |
| 31 | 5745 |
+----+------+
The result is as expected. But as you see, that function will be called twice. Now I want to know, how can I call that function once and store the result in a variable, then parse that variable?
You can simply place your query in a subquery and consume the value returned by the function in the outer query:
SELECT SUBSTRING_INDEX(sr,',', 1) AS s,
SUBSTRING_INDEX(sr,',', -1) AS r
FROM (
SELECT calculate_both_score_and_reputation(u.id) AS sr
FROM users u) AS t
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments