MySQL Explode a String?

Question

I'm trying to convert some PHP code to a MySQL stored proc. Here's the PHP code:

$aORs = array(); // create an empty array to hold the individual criteria to be OR'd...
$months = explode('|',$_REQUEST['sSearch_'.$i]);
$sWhere .= '(';
foreach($months as $month) {
    $year = intval(substr($month, 0, 4));
    $mon  = intval(substr($month, 5, 2));
    array_push($aORs, '(MONTH(e.StartDate) = '.$mon.' and YEAR(e.StartDate) = '.$year.')');
}
$sWhere .= implode(" OR ",$aORs); // transform the array of criteria into a properly delimited WHERE clause...
$sWhere .= ')';

I will be passing parameters to the stored proc like this:

2012-07|2012-10|2013-02

I'm having trouble figuring out how to parse the above input into a WHERE clause that looks like this:

(MONTH(e.sDate) = 07 AND YEAR(e.sDate) = 2012) OR
(MONTH(e.sDate) = 10 AND YEAR(e.sDate) = 2012) OR
(MONTH(e.sDate) = 02 AND YEAR(e.sDate) = 2013) OR

How can I do this in the stored proc? I've seen the "infamous" split_str function but I don't know how many values there may be.

spencer7593 · Accepted Answer · 2012-07-06 00:29:38Z

1

If your parameter values will always be well formed as you show them, a four digit year, a dash and a two digit month, separated by vertical bars with no white space, you could let the database do the search for you like this:

INSTR(CONCAT('|',parameter_value,'|'),DATE_FORMAT(e.sDate,'|%Y-%m|')) > 0

Or, to be a little more liberal with white space and the delimiters,

INSTR(parameter_value,DATE_FORMAT(e.sDate,'%Y-%m')) > 0

I don't think those will be any worse performance-wise, than running the MONTH and YEAR functions on the date column and doing equality comparison to literals. Neither of these predicates is going to be sargable (that is, allow an index to be used).

This approach has the benefit of less work to do in the stored procedure, since you won't have to dynamically create a SQL statement and pass in a variable number of arguments. (Less code to test.)

If you need to allow for the parameter to be optional, that is, when the parameter is not supplied (default it to an empty string), you can avoid applying the predicate on the date column in the same exact statement, with no changes to the SQL text:

(paramater_value = '' OR INSTR(parameter_value,DATE_FORMAT(e.sDate,'%Y-%m')) > 0)

To have the predicate be sargable, I would want my SQL to reference the native date column. I would write it as range scans, so my SQL text would be of the form:

(  ( e.sDate >=          CONCAT('2012-07','-01') AND
     e.sDate <  DATE_ADD(CONCAT('2012-07','-01'),INTERVAL 1 MONTH) )
OR ( e.sDate >=          CONCAT('2012-10','-01') AND
     e.sDate <  DATE_ADD(CONCAT('2010-10','-01'),INTERVAL 1 MONTH) )
OR ( e.sDate >=          CONCAT('2013-02','-01') AND 
     e.sDate <  DATE_ADD(CONCAT('2013-02','-01'),INTERVAL 1 MONTH) )

And having the ability to use an index is the only good reason I would go to all the bother of parsing out the parameter string, building up query text with a variable number of predicates, and using dynamic SQL, and the trouble of testing it all. I'd almost rather create a temporary table and load it with the "yyyy-mm" values parsed from the parameter string, and reference that in a join predicate.

CREATE TEMPORARY TABLE my_temp_parameter
( yyyy  SMALLINT          NOT NULL COMMENT 'year yyyy'     
, mm    TINYINT  UNSIGNED NOT NULL COMMENT 'month, 1-12'
, PRIMARY KEY (yyyy,mm)
)

(I'd definitely want a unique constraint on those two columns, because I wouldn't want my join generating "extra" rows.) In my query, I would reference the temporary table something like this:

FROM ... e
JOIN my_temp_parameter p
  ON e.sDate >= CONCAT(p.yyyy,'-',p.mm,'-01') AND 
     e.sDate < DATE_ADD(CONCAT(p.yyyy,'-',p.mm,'-01'),INTERVAL 1 MONTH)

The advantage to this approach is that I'm not having to debug dynamic SQL, and maintaining code that generates SQL text. I would prefer to have static SQL text that is more easily tested.

I apologize if that doesn't answer your question. But before I go down the road of creating dynamic SQL inside a stored procedure (and there are cases where it is called for), I will try out other approaches, and fall back to the dynamic SQL if I can't find another suitable solution.

edited Jul 6, 2012 at 0:29

answered Jul 5, 2012 at 22:11

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

3 Comments

MB34 Over a year ago

The parameters will be well-formed and there maybe more than 1. I am not going to modify the structure of the table.

MB34 Over a year ago

The first one using the INSTR worked perfectly. I don't have to parse out the values any more. It will also work for states as we pass them the same way.

spencer7593 Over a year ago

@MB34: glad to hear it's working for you. You say you aren't going to modify the structure of the table. I don't see any reason you would need to do that. I don't believe that I anywhere suggested anywhere that a change to the structure of your table would be required, or that it would be of any benefit. (Following my suggestion about the INSTR approach, I was only suggesting that to have a query make use of an available index, you night need a more elaborate query.)

Collectives™ on Stack Overflow

MySQL Explode a String?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related