December 17, 2008 at 11:13 am
I have a query where the datatype I'm ordering by is varchar(10) but when I use the ORDER BY the values come out like this: How do I work around this? Thanks!
100
10000
10060
10077
10077
101
December 17, 2008 at 11:25 am
If you are sure that the values are all numeric, you can do this:
ORDER BY
CAST(YourColumn as INT)
December 17, 2008 at 11:31 am
The field is alpha numeric. When I tried your solution here is the error message I received:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'R00190' to data type int.
Is there another datatype I can convert it to that will work? Thanks
December 17, 2008 at 11:39 am
Okay, I did say if you knew if your data was ALL numeric. You will need to analysis your data first and see what you have. Do you have any data that looks like this 0R00012?
Once you know your data, you can look at possibile solutions.
Based on what you have provided so far, I'm not willing to throw out another suggestion yet. I'll wait until you can tell me more about your data first.
December 17, 2008 at 3:04 pm
dejanette.gordon (12/17/2008)
I have a query where the datatype I'm ordering by is varchar(10) but when I use the ORDER BY the values come out like this: How do I work around this? Thanks!
There is a lot of solutions.
But it's not clear what to solve.
Can you ask a PROPER QUESTION first?
What is the order of values you want to return?
Lynn gave you perfect answer on your question.
If it does not work then your question was incorrect.
Please fix it.
_____________
Code for TallyGenerator
December 17, 2008 at 3:38 pm
Dejanette,
It would be a huge help to us if you'd post the actual data you're trying to sort in a readily consumable format along with a table creation statement. Please see the link in my signature for how to do that. Thanks.
--Jeff Moden
December 17, 2008 at 4:05 pm
Well, not knowing what you are working with, and deciding to take a swag at it, try out the following code and see if that gets you going in the right direction. I would recommend reading the article that Jeff suggested (and you will also find a link to below in my signature block as well). The guidelines it provides for posting questions will greatly benefit you and us when you have other questions.
We will also be able to test our code which further benefits you as well.
create table #MyTest (
TestID varchar(10) not null,
TestData varchar(100) not null
);
insert into #MyTest (
TestID,
TestData
)
select 'R000234','Some data' union all
select '100','Some more data' union all
select '101','Some more data again' union all
select '1000','And more data' union all
select 'U1023','and even more data';
select
*
from
#MyTest
order by
TestID;
select
*,
case when left(TestID,1) like '%[A-Za-z]%'
then left(TestID,1) + right('0000000000' + substring(TestID,2,len(TestID) - 1), 9)
else right('0000000000' + TestID, 10)
end
from
#MyTest
order by
case when left(TestID,1) like '%[A-Za-z]%'
then left(TestID,1) + right('0000000000' + substring(TestID,2,len(TestID) - 1), 9)
else right('0000000000' + TestID, 10)
end;
drop table #MyTest;
December 17, 2008 at 4:40 pm
Thanks for the replies!! I do apologize for not posting my question in the proper format. It's my first time posting, although I'm not looking for a pass based on my own ignorance of how things work around here. Here is my attempt at asking the question properly:
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
instead of what is posted in the original post. In the meantime, I will read through the link on how to post properly, however I would appreciate if anyone would be willing to help me figure out this problem if I have communicated the issue properly. Much Appreciated! DG
December 17, 2008 at 4:54 pm
That is essentially the question you asked originally. The problem was that the information you showed did not match reality. You showed values that were all numeric. If that is the case, then what I gave you originally will work.
The problem turned out that you have non-numeric data in the alphanumeric field, so my solution failed. To determine how to sort your data, you have to understand the data. This means knowing the range of values contained in the field. Obviously, it isn't purely numeric data stored as characters. If the data is mostly numeric, with non-numeric values such as R1000 (a single leading character), then the solution I just posted would be a viable alternative.
December 17, 2008 at 8:20 pm
dejanette.gordon (12/17/2008)
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
The answer is:
ORDER BY AlphanumericValue
_____________
Code for TallyGenerator
December 17, 2008 at 11:27 pm
dejanette.gordon (12/17/2008)
Thanks for the replies!! I do apologize for not posting my question in the proper format. It's my first time posting, although I'm not looking for a pass based on my own ignorance of how things work around here. Here is my attempt at asking the question properly:How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
instead of what is posted in the original post. In the meantime, I will read through the link on how to post properly, however I would appreciate if anyone would be willing to help me figure out this problem if I have communicated the issue properly. Much Appreciated! DG
Ummmm.... read the link on how to post properly first. And, Lynn is correct... you haven't posted data that represents your real data.
--Jeff Moden
December 17, 2008 at 11:27 pm
Sergiy (12/17/2008)
dejanette.gordon (12/17/2008)
How do you sort alphanumeric data in sequential order? I need the data to go from:
100
101
102
etc...
The answer is:
ORDER BY AlphanumericValue
Now, THAT's entertainment! 😀
--Jeff Moden
December 18, 2008 at 12:04 pm
:hehe: Thanks for the laughs...my future posts on this forum will not be subpar.
December 18, 2008 at 12:23 pm
dejanette.gordon (12/18/2008)
:hehe: Thanks for the laughs...my future posts on this forum will not be subpar.
I'm glad to see that you have a sense of humor! It is needed around these parts at times! :w00t:
December 18, 2008 at 12:29 pm
It sure is...it can be downright grueling and discouraging for a newbie if one takes it personally. But hey, my post was kind of simple and "cheesy" based on how it was supposed to look. I didn't know any better, but I trust me, you will NEVER see me post without following the standards 😛 I was cracking up at the last post from Jeff about entertainment...LOL You've gotta take it all in stride though. This will only help me out as well as the next newbie (not sure if you get many around here??) from being made an example of...still LOL at myself.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply