mysql query with explode

Question

I have a table with this type of value 20|10|5|8|19| (with separator)

I need to select rows, where first value (for example after explode), less than 20.

$arr = explode("|", "goal_times");
$first_goal_time = $arr[0];

But how to do this in Mysql query?

I need to have number of rows with this condition. – Hayk Makyan Jul 24 '15 at 08:59 — Hayk Makyan, Jul 24 '15 at 08:59

score 1 · Answer 1 · answered Jul 24 '15 at 08:58

1

In general you shouldn't have multiple values with separator in the same column. In this case you can get away with SUBSTRING_INDEX()

SELECT * 
 FROM yourtable 
 WHERE 
  SUBSTRING_INDEX(yourcolumn,'|',1) < 20;

answered Jul 24 '15 at 08:58

vhu

Add a silent conversion of string to integer as `SUBSTRING_INDEX(yourcolumn,'|',1)+0` – Abhik Chakraborty Jul 24 '15 at 09:00
Is it not good idea to have multiple values in the same column? I need to save goals minutes, authors, tima for each game. – Hayk Makyan Jul 24 '15 at 09:08

1 Answers1