Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORDER BY Varchar field with numerics and slashes

Posted on 2006-12-01
3
Medium Priority
?
941 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 400 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 400 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 1200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
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…

916 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