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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7917
  • Last Modified:

How do I ORDER BY Alpha Numeric?

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
RickDai
Asked:
RickDai
1 Solution
 
HuyBDCommented:
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
 
HuyBDCommented:
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
 
imran_fastCommented:
or
SELECT location_tag
FROM location ORDER BY
case  when ISNUMERIC(location_tag) = 1 then cast(location_tag as int) else 999   end
0
 
Brendt HessSenior DBACommented:
....
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
 
RickDaiAuthor Commented:
@HuyBD
worked like a charm. thanks!

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now