0

I have a column with type string, and we saved all sizes and prices in this field it is separated by pipeline and semicolon sign as shown below 

10;100|11;111|12;112|13;130|14;130|15;105

I want to get the minimum price in this field (price;size where 10 is size and 1502 is price).

I used this query but it returns only one of them and not the minimum

select SUBSTRING_INDEX((textColumn), '|', 1) as sizeAndprice from myTable;
frlan
  • 6,950
  • 3
  • 31
  • 72
Moh
  • 1
  • 1
  • Why are the data stored this way? Is it a legacy thing, or can that design be changed? It's not exactly optimal to store data that way and means it's harder to easily find the data, as you are currently finding. – gabe3886 Dec 14 '15 at 16:23
  • we did that to minimize the number of records – Moh Dec 14 '15 at 16:26
  • You have to write a stored procedure or use logic in the application code to get the minimum price out of this structure. – Shadow Dec 14 '15 at 16:32
  • If you look at http://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two, you can see how it's done for splitting into two columns. This could be expanded for multiple, but I'm guessing there's not a set limit to this. As mentioned by @shadow, you'll need to do this outside of a typical query. Databases aren't really designed to be used like this. – gabe3886 Dec 14 '15 at 16:39

1 Answers1

0

If the structure can be changed, I would suggest in doing so.
However, if it cannot be changed whatever the reason, I would fetch the entire row as it is and use the explode() function: 

/* fetch the whole column as string in a variable ($myWholeText for this example) */
$mySplitColumns = explode ("|", $myWholeText );

You will then have all the split values (both price and size) in a separate index of array $mySplitColumns, and you will be able to sort them out as you like.

For more reference about the explode function, check this link

Jordi
  • 46
  • 3