Use an EXPLODE exp in an ORDER clause

Anon

Hello,

is it possible to use an Explode clause as the ORDER statement?

i need to select records from a table and then order them by one of the feilds. I know i can do that normally by smiply using the feild.

i have a feild that has multiple categories imploded in it.

here is the example:

id : name : category
1 : prod1 : |139|140|
2 : prod2 : |13|1120|

3 : prod3 : |60|555|

I would need to explode the category results and use the first result as the number to order the list by.

what i want:
select * from products where brand = 'brand' ORDER by 'explode("|",(trim($numbers,"|")))' ASC

is this possible?

Anon

Maybe you can try a query of this kind:

select * from table order by SUBSTRING_INDEX(category,"|",1),SUBSTRING_INDEX(category,"|",1) ASC

You would add as many SUBSTRING_INDEX("category","|",1) for as many | you have in the field. I don't really know if this is what you want, but is worth trying.

Hope this helps

fLIPIS

Anon

some of the feilds have a few categories (ex. |123|3267|7823| ) and another can have one (ex. |34| )

i have looked on mysql.com and cannot find out what SUBSTRING_INDEX is.

i only need to choose the first one and then order the results by that.

is SUBSTRING_INDEX going to help me do that?

thanks for the advice

theslinky

Hi,

Yes, substring_index will get you the string you need...

SELECT , SUBSTRING(SUBSTRING_INDEX(category, '|', 2), 2) 1 AS sortdata FROM products ORDER BY sortdata ASC

Basically, the substring_index will return a string like |123 or |NNN (the first category number). So we run ANOTHER substring on this so we get just 123 or NNN. Then we multiply by 1 to convert it into an integer (usefull if you are comparing 21 and 121 -- if it was a string, 121 would come first). Then finally we make it into a variable by saying "AS sortdata". Lastly we order it using ORDER BY sortdata ASC

hope this work!
-sridhar