CODEWITHSUNDEEP
sql
bhai
bhai

Reputation: 305

sql Order by Query to display the name in order

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

Upvotes: 0

Views: 245

Answers (4)

bvr
bvr

Reputation: 4826

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

Upvotes: 2

Alex
Alex

Reputation: 8937

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.

Upvotes: 3

TechDo
TechDo

Reputation: 18659

Please try:

select *
From tbl
order by CAST(SUBSTRING(col, PATINDEX('%[0-9]%', col+'0'), 10) as int)

Upvotes: 1

Justin
Justin

Reputation: 9724

Query:

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

Result:

|    COL |
----------
|  rama1 |
|  rama2 |
|  rama3 |
| rama10 |
| rama11 |

Upvotes: 1

Related Questions