0

I am using oder by name in my sql query.
The following is the name in my table. 

rama1  
rama2  
rama10    
rama3  
rama11

I am using the query to display the name order by name
the output is coming like, 

rama1  
rama10  
rama11  
rama2  
rama3

But I need the output should be, 

rama1  
rama2  
rama3  
rama10  
rama11

Please help me with the query to get the above output.

Thanks In advance

bhai
  • 305
  • 3
  • 9
  • 19
  • What RDBMS are you using? – peterm Jul 04 '13 at 06:44
  • 1
    Do you have word "rama" in all records? Does all records end with numbers, you have to order by? – Alex Jul 04 '13 at 06:44
  • @voo..yes..ram is there in all rows in my table and it ends with the number – bhai Jul 04 '13 at 06:46
  • does it matter? his Problem is that when sorting alphabetically 10 is sorted before 3 because 1 comes before 3. if the DBS does not support this kind of sorting, i think you'll need to do the sorting "by hand" maybe in a procedure – billdoor Jul 04 '13 at 06:47
  • 1
    @billdoor Yes, IT DOES MATTER, because in this case he can make select this removing "rama" part, then convert it to numeric and make proper order. Such sorting is used in most RDBMS for string datatypes. Tell me what DBS supports the kind of sorting of strings he wants? – Alex Jul 04 '13 at 06:58
  • -1 When you ask an SQL question, you have to identify the DBMS you are using. Different vendors have different implementations, especially in string and date functions. – ypercubeᵀᴹ Jul 04 '13 at 07:40

4 Answers4

3

I suppose you have a wrong structure of your table. You should have a separate column, like ID of the numeric datatype, where you could keep your rama numeric part. In this case you would be able to make such queries without developing a bycicle.

In your case you can get numeric part from your string (see How to get the numeric part from a string using T-SQL? for ms sql) and order by it. But this is wrong way to go.

Community
  • 1
  • 1
Alex
  • 8,827
  • 3
  • 42
  • 58
2

Try this

SELECT col FROM Table1
ORDER BY 
CASE WHEN PatIndex('%[0-9]%',col) > 0
      THEN RIGHT(col,LEN(col)- (PatIndex('%[0-9]%',col)-1)) * 1
      ELSE col END

DEMO

Community
  • 1
  • 1
bvr
  • 4,786
  • 1
  • 20
  • 24
1

Query:

SELECT t1.*
FROM Table1 t1
ORDER BY CAST(REPLACE(t1.col, 'rama', '') AS UNSIGNED) ASC

Result:

|    COL |
----------
|  rama1 |
|  rama2 |
|  rama3 |
| rama10 |
| rama11 |
Justin
  • 9,634
  • 6
  • 35
  • 47
1

Please try:

select *
From tbl
order by CAST(SUBSTRING(col, PATINDEX('%[0-9]%', col+'0'), 10) as int)
TechDo
  • 18,398
  • 3
  • 51
  • 64