1

I have a table,

incassoid  Amount      Details
=========  ======   ===============
   1       1.0000   5||0.4999;7||0.0001;9||0.0500
   2       2.0000   3||1.0000;15||1.0000
   3       1.0000   8||1.0000

where in detail column, 5||0.4999 means 5 is a productid and 0.4999 is the money product has,what I need is to get the values from the details section and represent it in the table, It needs to loop over all the incassoid, and for each incassoid, I need the details like this, As an example for incassoid 1, it should show its detail like this,

incassoid  Productid   productamount   amount  
=========  =========   ============   =========
   1           5          0.4999         1
   1           7          0.0001         1
   1           9          0.0500         1

I am trying to find a way to parse the details section, but I am not sure how should I do this, Can some one please help me out in this.

Thanks!!!

ebahi
  • 536
  • 2
  • 7
Nia
  • 151
  • 1
  • 12
  • I guess you already have noticed, but never store data like that. Have separate columns for the different details. – jarlh May 10 '16 at 07:28
  • Possible duplicate of [SQL split values to multiple rows](http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) – mitkosoft May 10 '16 at 07:36
  • Even if you do that somehow, this is never going to be efficient enough to put it into production. You should have separate table for details that should look exactly like the result that you expect. – Jakub Matczak May 10 '16 at 07:36
  • Hey guys, thank you...I also think that data should not be stored like this, but the place where I do my internship, have it like this. I think they should change it but they have it for so many years that they don't want to do that :( So I was trying to parse it now. – Nia May 10 '16 at 07:44

1 Answers1

2

I suppose you have a table of products

Table products

  id
------
    3
    5
    7
    8
    9
   15

You can use that request to do what you want

select t.incassoid, p.id productid,
       substring_index(substring_index(t.details, concat(p.id, '||'), -1), ';', 1) productamount,
       t.amount
from products p, tablename t
where find_in_set(p.id, (select group_concat(p2.id)
                         from products p2, tablename t2
                         where t2.incassoid = t.incassoid
                           and (t2.details like concat(p2.id, '||%')
                                or t2.details like concat('%;', p2.id, '||%'))))

The subrequest in the find_in_set extract a list of product ids from details. Then, for each details (of tablename), the find_in_set filter is used to join the correct list of products.

After that we just have to extract the amount of product in details.

SQLFiddle : http://sqlfiddle.com/#!9/7dfd4/1/0

ebahi
  • 536
  • 2
  • 7