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

Question

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 !!

Use substring() function to strip off the first characters. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 20, 2011 at 11:56
ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING(product_name,5),' ',1) AS INT) might work. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 20, 2011 at 12:00

Johan · Accepted Answer · 2011-07-20 12:01:43Z

2

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

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.

answered Jul 20, 2011 at 12:01

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-07-20 12:06:33Z

0

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)

answered Jul 20, 2011 at 12:06

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

a'r · Accepted Answer · 2011-07-20 12:11:11Z

0

There are a couple of options:

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

answered Jul 20, 2011 at 12:11

a'r

37.2k7 gold badges68 silver badges67 bronze badges