CODEWITHSUNDEEP
mysql
NawMem
NawMem

Reputation: 21

Mysql Find numbers in string and filter result by numbers in string

I have mysql table with has field product_name which has data something like

SKL-05
TIP-01
TIP-02 L/R
TIP-12 UV
TIP-120 L/R
TIP-121 L/R
TIP-13 L/R

I want to sort that result like as follow

SKL-05
TIP-01
TIP-02 L/R
TIP-12 UV
TIP-13 L/R
TIP-120 L/R
TIP-121 L/R

Means numberwise in the string 01, 02, 03 etc Please help me guys...

Thanks in Advance !!

Upvotes: 0

Views: 1421

Answers (3)

a'r
a'r

Reputation: 37029

There are a couple of options:

  1. Reformat the codes to allow them to be ordered naturally, eg.

     TIP-121 L/R     -->  TIP-121 L/R
 TIP-13 L/R      -->  TIP-013 L/R

  2. Split the string up into its parts and order on them individually. This can get a bit horrible if there are lots of parts. eg.

    SELECT code
FROM table
ORDER BY 
    SUBSTR(code, 1, 4), 
    CAST(SUBSTR(code, 5, LOCATE(' ', code)) AS UNSIGNED)

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

You can try using the SUBSTRING() and SUBSTRING_INDEX() functions of MySQL: String functions

Try:

ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING(product_name,5),' ',1) AS INT)

or

ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(product_name,' ',1),'-',-1) AS INT)

Upvotes: 0

Johan
Johan

Reputation: 76753

SELECT product_name FROM table1 
ORDER BY substring(product_name FROM 5)

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

This will kill any index you have on product_name though, if you need speed, add an extra field numeric_product_name with an index on that.

Upvotes: 2

Related Questions