Ordering based on number value in a string from SQL

Question

I have these CB1-15 values however I want to order them by... CB1-15 but currently it goes CB1, CB10, CB11 etc.

This is how my table looks...

CB    Description   File Path

CB1   New Product   new.png
CB10            
CB11            
CB12            
CB13            
CB14            
CB15            
CB2   Best Seller   best.png
CB3         
CB4         
CB5         
CB6         
CB7         
CB8         
CB9

Thanks for helping :)

Just so I'm clear. is the sample data one column or multiple? — John Cappelletti
– John Cappelletti, Commented Sep 24, 2016 at 3:35
@JohnCappelletti it's been answered - however if anyone searches it later on iv'e updated the post to show its columns — Adam91Holt
– Adam91Holt, Commented Sep 25, 2016 at 2:36

Unnikrishnan R · Accepted Answer · 2016-09-24 03:26:39Z

6

Use the below script.

  SELECT * 
  FROM  YourTable
  ORDER BY CAST(Replace(YourColumn,'CB','')as INT)

answered Sep 24, 2016 at 3:26

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

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

1 Comment

Adam91Holt Over a year ago

Thank you :) Much appreciated!

sgeddes · Accepted Answer · 2016-09-24 03:20:49Z

2

It's a little unclear if that data is in multiple columns or a single string. Here's one method that should work in both cases:

select *
from yourtable
order by cast((substring(yourfield, 3, 2)) as int)

SQL Fiddle Demo

The key is using cast to order by an int instead of the string.

answered Sep 24, 2016 at 3:20

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

2 Comments

Adam91Holt Over a year ago

Thank you ever so much sir :)

Shushil Bohara Over a year ago

This doesn't work for CB100 so you have to use the length of value instead of 2 by replacing CB, So I would say @Unnik's solution work better.

Gordon Linoff · Accepted Answer · 2016-09-24 11:51:14Z

0

Another simple method do do the ordering:

order by len(col), col

And if you want descending:

order by len(col) desc, col desc

answered Sep 24, 2016 at 11:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Ordering based on number value in a string from SQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related