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?

vhu · Accepted Answer · 2015-07-24 08:58:18Z

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, 2015 at 8:58

vhu

12.9k11 gold badges42 silver badges50 bronze badges

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

2 Comments

Add a silent conversion of string to integer as SUBSTRING_INDEX(yourcolumn,'|',1)+0

Is it not good idea to have multiple values in the same column? I need to save goals minutes, authors, tima for each game.