Solved

ORDER BY Varchar field with numerics and slashes

Posted on 2006-12-01
3
928 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now