splitting strings in mysql row

Question

I've been searching the internet and found an 'almost' solution to my problem. I am using the query: 'MYSQL'

SELECT distinct company,price,material,`contractors_parameters`.`width`,`contractors_parameters`.`height`
from `contractors_parameters`
join `Windows_last_submissions` `wls`
where `wls`.`width` in (SELECT  SPLIT_STR(`contractors_parameters`.`width` /*string*/
                                           , ',' /*delimiter*/
                                           , 1 /*start_position*/
                                           ))
and `wls`.`height` in (SELECT SPLIT_STR(`contractors_parameters`.`height` /*string*/
                                           , ',' /*delimiter*/
                                           , 1 /*start_position*/
                                           ))
and `contractors_parameters`.`material` = `wls`.`chosenmaterial`
and `contractors_parameters`.`type` = `wls`.`type`
and `contractors_parameters`.`price` <= `wls`.`maximumbid`
ORDER BY `contractors_parameters`.`company` ASC

In my "width" and "height" columns..there may be multiple integers as thus: "22,23,24,25,". I have used the SPLIT_STR function (not available with fiddle I guess) to separate these numbers as separate strings and query againsts them using the IN clause.
It almost works except if the dimensions in 'contractors_parameters' are width=24,25 height=48,49,50 and the dimensions in 'Windows_last_submission' (this isthe table that I'm querying) - it will work..but if the dimensions from 'contractors_paramaters' are reversed, or in a different order, say width=25,24 height=48,49,50 - it will nOt work. Anyone have any advice? I tried to use fiddle but it errors when I use the SPLIT_STR() function.

dare we ask why in the world the data is saved that way ? were you trying to help out the db engine ? — Drew, Sep 10 '15 at 19:38
There is no `SPLIT_STR()` function in MySQL. Where did you get the idea that this function exists? — Barmar, Sep 10 '15 at 20:31

score 3 · Accepted Answer · answered Sep 10 '15 at 19:43

3

Normalize your database schema and do it the right way. Otherwise your sql experience will be a patchwork/ band-aid trainwreck. That's my Answer, and I am sticking with it.

answered Sep 10 '15 at 19:43

Drew

24,851
10
43
78

I realize it's bad practice for the database. Unfortunately, my goal is for companies to insert different parameters but for the same company - so normalizing is out of the question with this table. – Casey Stack Sep 10 '15 at 19:58
Normalizing is never out of the question :) Nothing that you just commented would suggest that it is. They can write the info with lipstick on a napkin. Send it in by pigeon. I would save it normalized. You kill performance, won't be using indexes, life of a tablescan jail cell after you torture yourself writing the code to even get there. Just my opinion, worth the paper it isn't even written on. – Drew Sep 10 '15 at 20:00

durbnpoisn · Answer 2 · 2015-09-10T19:42:27.497

1

Setting aside the fact that your data shouldn't be store that way... Your method of retrieving it should be fine without having to split the string in the query.

You can use JavaScript to split it once you've got it (and that DOES work in a fiddle)

var str = "23,24,25";
var res = str.split(",");

If you don't like JS and would rather keep it to PHP:

<?php
// Delimiters may be slash, dot, or hyphen
$date = "04/30/1973";
list($month, $day, $year) = split('[/.-]', $date);
echo "Month: $month; Day: $day; Year: $year<br />\n";
?>

That should be much easier than trying to do it at the SQL level.

edited Sep 10 '15 at 19:42

answered Sep 10 '15 at 19:40

durbnpoisn

4,666
2
16
30

you just skipped the whole php level.. at least the question had a php tag.. – I wrestled a bear once. Sep 10 '15 at 19:42
i revoke my previous comment – I wrestled a bear once. Sep 10 '15 at 19:42
I noticed that too. So I added that in. – durbnpoisn Sep 10 '15 at 19:42

score 1 · Answer 3 · answered Sep 10 '15 at 20:29

Your STR_SPLIT function is only returning one value, not the array of values you expect. If you absolutely MUST use the data in this structure, then the only way you'll get it to work is something like the following hideous query

SELECT distinct company,price,material,`contractors_parameters`.`width`,`contractors_parameters`.`height`
from `contractors_parameters`
join `Windows_last_submissions` `wls`
where `wls`.`width` in (
      SELECT  SPLIT_STR(`contractors_parameters`.`width` /*string*/
                                           , ',' /*delimiter*/
                                           , 1 /*start_position*/)
                                           union
      SELECT  SPLIT_STR(`contractors_parameters`.`width` /*string*/
                                           , ',' /*delimiter*/
                                           , 2 /*start_position*/)
                                           union
      SELECT  SPLIT_STR(`contractors_parameters`.`width` /*string*/
                                           , ',' /*delimiter*/
                                           , 3 /*start_position*/)
                                           union
      SELECT  SPLIT_STR(`contractors_parameters`.`width` /*string*/
                                           , ',' /*delimiter*/
                                           , 4 /*start_position*/)
                                           )
and `wls`.`height` in (
    SELECT SPLIT_STR(`contractors_parameters`.`height` /*string*/
                                           , ',' /*delimiter*/
                                           , 1 /*start_position*/)
                                           union
    SELECT SPLIT_STR(`contractors_parameters`.`height` /*string*/
                                           , ',' /*delimiter*/
                                           , 2 /*start_position*/)
                                           union
    SELECT SPLIT_STR(`contractors_parameters`.`height` /*string*/
                                           , ',' /*delimiter*/
                                           , 3 /*start_position*/)
                                           union
    SELECT SPLIT_STR(`contractors_parameters`.`height` /*string*/
                                           , ',' /*delimiter*/
                                           , 4 /*start_position*/)
                                           )
and `contractors_parameters`.`material` = `wls`.`chosenmaterial`
and `contractors_parameters`.`type` = `wls`.`type`
and `contractors_parameters`.`price` <= `wls`.`maximumbid`
ORDER BY `contractors_parameters`.`company` ASC;

You'll need to include a union line for the maximum number of comma separated values you expect in the contractors_parameters.width or height columns.

splitting strings in mysql row

3 Answers3