Solved

How do I ORDER BY Alpha Numeric?

Posted on 2006-06-20
5
7,750 Views
Last Modified: 2008-01-09
I have a problem with sorting a alpha numeric field.

location_tag (type nvarchar(50))
1
2
3
4
10
11
20
Pad A
Pad B

If I do :
"SELECT location_tag from location ORDER BY location_tag"
1
10
11
2
20
3
4
Pad A
Pad B

How do I make it so it sorts the numeric values as integers then sort the character values?

I have tried:
SELECT location_tag FROM location ORDER BY CASE ISNUMERIC(location_tag) WHEN 1 THEN CONVERT(INT, location_tag) ELSE location_tag END

But it would produce an error:
Syntax error converting the nvarchar value 'Pad A' to a column of data type int.


Any Help is appreciated.
I know that i shouldnt be using a mixed field that contains both alpha and numeric, but that is how the client wants it. Because sometimes the values are numeric, somtimes its characters, sometimes both.

It is when its both that i am encountering a problem.
0
Comment
Question by:RickDai
5 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 16948083
You can create new 2 fields to store int and char

SELECT location_tag,
case when ISNUMERIC(location_tag) =1 then CONVERT(INT, location_tag) else -1 end as fint,
case  when ISNUMERIC(location_tag)<>1 then location_tag else '0' end as fchar
 FROM location ORDER BY fchar,fint
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 125 total points
ID: 16948125
Or

SELECT location_tag
FROM location ORDER BY
case  when ISNUMERIC(location_tag)<>1 then location_tag else '0' end,
case when ISNUMERIC(location_tag) =1 then CONVERT(INT, location_tag) else -1 end
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16949498
or
SELECT location_tag
FROM location ORDER BY
case  when ISNUMERIC(location_tag) = 1 then cast(location_tag as int) else 999   end
0
 
LVL 32

Expert Comment

by:bhess1
ID: 16954102
....
ORDER BY
    RIGHT(REPLICATE(N' ', 50) + location_tag, 50)

This will right justify your data for ordering.  If the data is:

1
10
11
2
20
3
4
Pad A
Pad B
1A
A1
1X

 the returned set is:

1
2
3
4
10
11
1A
1X
20
A1
Pad A
Pad B
0
 
LVL 2

Author Comment

by:RickDai
ID: 16955202
@HuyBD
worked like a charm. thanks!

@everyone
thanks for your input, much is appreciated.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

11 Experts available now in Live!

Get 1:1 Help Now