Solved

ORDER BY Varchar field with numerics and slashes

Posted on 2006-12-01
3
937 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting a SQL script 5 41
Backing up Large SQL Server VM Best practice [using Veeam Backup] 8 75
sql trace 4 28
database audit for object access 6 42
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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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