Sql query to select data in asc desc order with find number in string

Question

How can I find the number from string with a SQL query so I select data in asc and desc order with this number?

I have table bf_offers and a column title with values like this:

title --------------- Flat 20% off Flat 50% off Upto 40% off

I want to find the number like 20, 50 & 40 and arrange title with in desc or asc order. If I set to desc order then data should be ordered like this:

title ----------------- Flat 50% off Flat 40% off Upto 20% off

Zafar Malik · Accepted Answer · 2015-07-03 05:35:04Z

2

You can try it-

ORDER BY CAST(RIGHT(SUBSTRING_INDEX(title,'%',1),2) AS SIGNED) DESC

answered Jul 3, 2015 at 5:35

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

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

Comments

Mukesh Kalgude · Accepted Answer · 2015-07-03 05:29:24Z

0

Using substring function do this.

order by SUBSTRING(title, CHAR_LENGTH(title) - 5,2)

or descending

order by SUBSTRING(title, CHAR_LENGTH(title) - 5,2) desc

answered Jul 3, 2015 at 5:29

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

1 Comment

Gitesh Purbia Over a year ago

Colum pattern can be diffrent. it is not in fixed patter. it can also like :- 50% off, 50% flat discount etc. @mukesh

Emipro Technologies Pvt. Ltd. · Accepted Answer · 2015-07-03 06:06:18Z

Try following,

SELECT CAST((SELECT SUBSTRING(title, n, 1) FROM tablename WHERE n <= LEN(title) AND SUBSTRING(titale, n, 1) LIKE '[0-9]' FOR XML PATH('')) AS float) FROM tablename

Collectives™ on Stack Overflow

Sql query to select data in asc desc order with find number in string

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related