Mysql - splitting table contents, order amount and its description

Question

I'm having a problem with a mysql query, i have a table which contains both the amount of an order (the first int in the table) and it's description. Unfortunately i cant change the database structure. I need to split these in a query, i have the following query;

Load table:
3 pallets 120 x 100 x 100
12 pallet 120 x 100 x 84


SELECT *,
IF (load * 1! = 0, LEFT (load, 1), '1 ') AS cargo_quantity,
IF (load * 1! = 0, SUBSTR (load, 2), load) AS cargo_description
FROM transport

The query works when the amount is below 10, when the number is 10 or higher the query will fall short. I could really use some help solving this problem, any suggestions?

So you have a field which contains more than one piece of data, and you'd like the database engine to break those pieces apart for you... how did the pieces get joined together in the first place? I suspect not in the database engine, but perhaps in your application code? So why not split apart in your application code too? If you really want the database to do this for you, it's an indication that the pieces really should be stored in separate columns - which raises the question of *why* you "*cant change the database structure*"? — eggyal, Apr 26 '13 at 08:50
look [this](http://stackoverflow.com/questions/2696884/mysql-split-value-from-one-field-to-two). in your case this could proably work: SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(load, ' ', 1), ' ', -1) as cargo_quantity, SUBSTRING_INDEX(SUBSTRING_INDEX(load, ' ', 2), ' ', -1) as cargo_description FROM transport; — Bojan Kovacevic, Apr 26 '13 at 09:01
please give a bigger range of example rows. especially the ones with "unusal" format — luksch, Apr 26 '13 at 10:00

luksch · Accepted Answer · 2013-04-26T10:11:47.133

If you are sure that the first "column" of your entry is always seperated by " " then you can do this:

SELECT *, 
  LEFT(loadcolumn, LOCATE(" ",loadcolumn)) AS cargo_quantity,
  SUBSTR(loadcolumn, LOCATE(" ",loadcolumn)+1)  AS cargo_description
  FROM transport

and here a sqlfiddle with a working solution:

Note that "load" seems to be a mysql keyword. I am not sure, so in my sqlfiddle I renamed it.

EDIT:

here is the improved solution if your "first column" does not exist:

SELECT *, 
IF (concat('',(LEFT(loadcolumn, LOCATE(" ",loadcolumn))) * 1) = 
    LEFT(loadcolumn, LOCATE(" ",loadcolumn)), 
    LEFT(loadcolumn, LOCATE(" ",loadcolumn)), '1') AS cargo_quantity,
IF (concat('',(LEFT(loadcolumn, LOCATE(" ",loadcolumn))) * 1) = 
    LEFT(loadcolumn, LOCATE(" ",loadcolumn)), 
    SUBSTR(loadcolumn, LOCATE(" ",loadcolumn)+1), loadcolumn) AS cargo_description
FROM transport

improved sqlfiddle

There isn't always a space, some field don't have a value at the start, like "PALLET 120 x 100 x 82" when this occurs the value should be 1. I think i'll need some sort of else if statement to solve this — Nico, Apr 26 '13 at 09:52
i improved my answer to tackle this problem. if you like it, vote me up :) — luksch, Apr 26 '13 at 10:24

Mysql - splitting table contents, order amount and its description

1 Answers1