Solved

ORDER BY Varchar field with numerics and slashes

Posted on 2006-12-01
3
936 Views
Last Modified: 2010-05-18
I am trying to ORDER BY a column that is a varchar data type, but it has numerics and slashes in it.  It is not ordering it correctly, any ideas?  I would like it to sort in numerical order, then after the numerics, sort in alphabetical order.  lHere are the values being returned in ascending order:
                                                                     
24/7.75Z                                  
24/7Z CB                              
6/104                              
6/105

Thanks,

JP


0
Comment
Question by:FreightTrain
3 Comments
 
LVL 13

Assisted Solution

by:Atlanta_Mike
Atlanta_Mike earned 100 total points
ID: 18056709
If there will always be a number to the left of the slash...


ORDER BY convert(int,LEFT(ColumnName,CHARINDEX('/',ColumnName)-1)), ColumnName

Or some variation. Might have to use a case statement.

0
 
LVL 5

Assisted Solution

by:CIC Admin
CIC Admin earned 100 total points
ID: 18056760
order by
    cast(substring(column, 1, charindex('/', column)-1) as integer),
    substring(column, charindex('/', column)+1, len(column))

The second sort is everything after the slash (your second requirement).  It assumes every record has a slash in it.
0
 
LVL 2

Accepted Solution

by:
AtulKamdar earned 300 total points
ID: 18058947
Hi,

Just use this one. Add as many u want. this will give u for Varchar(10).just replace ur field name to URFIELDNAME.

Order by ASCII(SUBSTRING("URFIELDNAME", 0, 1)),
ASCII(SUBSTRING("URFIELDNAME", 1, 1)),
ASCII(SUBSTRING("URFIELDNAME", 2, 1)),
ASCII(SUBSTRING("URFIELDNAME", 3, 1)),
ASCII(SUBSTRING("URFIELDNAME", 4, 1)),
ASCII(SUBSTRING("URFIELDNAME", 5, 1)),
ASCII(SUBSTRING("URFIELDNAME", 6, 1)),
ASCII(SUBSTRING("URFIELDNAME", 7, 1)),
ASCII(SUBSTRING("URFIELDNAME", 8, 1)),
ASCII(SUBSTRING("URFIELDNAME", 9, 1)),
ASCII(SUBSTRING("URFIELDNAME", 10, 1))
 
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question