How to split Column info and select only a subset of subfields ?

[#1]

Hi,

I need to parse a column which has the following format (e-commerce cart)

a:2:{i:0;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 1";s:7:"complex"}i:1;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 2";s:7:"paramet"}}

and I want to obtain from it , cart products which in this case they are:

The Name of My Service 1

The Name of My Service 2

and obviously the number of products is not always the same for all carts and even, the number of parameters for each product is not the same ... the only thing we now is that product Name attribute goes always after "name" attribute...

Any hint?

Thanks a lot!!

[maxgro]

see attachement

it would be useful to have some more records to check my script

[#3]

Thanks a lot!! IT works almost perfect!!

"where id > 1" condition  I suppose that it is to remove the first token but it does not work well because my table "cart" has a lot of records and id column is an incremental integer which is only 1 for the first"cart"...

[#4]

There is not a way to add a column indicating SubFieldIndex with values 1,..2, nbSubFields?  so after this we can filter

SubField(cart,'name') as col1,

SubFieldIndex,

-

-

where SubFieldIndex > 1

[#5]

If you use this in your w.qvw you will see what I mean

S:

LOAD * INLINE [

    col

    `'a:2:{i:0;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 1";s:7:"complex"}i:1;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 2";s:7:"paramet"}}`

    `'a:2:{i:0;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 1";s:7:"complex"}i:1;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 2";s:7:"paramet"}}`

    `'a:2:{i:0;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 1";s:7:"complex"}i:1;O:8:"stdClass":20:{s:4:"type";s:8:"activity";s:4:"name";s:32:"The Name of My Service 2";s:7:"paramet"}}`

];

[maxgro · Accepted solution]

hope it helps

added a flag to identify first of every group