Solved

ORDER BY Varchar field with numerics and slashes

Posted on 2006-12-01
3
938 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 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