RickDai
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or
SELECT location_tag
FROM location ORDER BY
case when ISNUMERIC(location_tag) = 1 then cast(location_tag as int) else 999 end
SELECT location_tag
FROM location ORDER BY
case when ISNUMERIC(location_tag) = 1 then cast(location_tag as int) else 999 end
....
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
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
ASKER
@HuyBD
worked like a charm. thanks!
@everyone
thanks for your input, much is appreciated.
worked like a charm. thanks!
@everyone
thanks for your input, much is appreciated.
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
FROM location ORDER BY fchar,fint