How to order varchar data by numeric char and then alphanumeric char

Question

0.00/5 (No votes)

See more:

Hello.
I have a varchar column named "Fig_Num" that contains numeric and alphanumeric values that I want to sort. For the data is:
1
2
3
3A
3B
4
5
6
7
8
9
10

I want the sorted data to show like above, but when I use ORDER BY Fig_Num I get:

1
10
1A
2
3
4
5
6
7
8
9

What I have tried:

SELECT * FROM Figs
WHERE Job_ID=64895 ORDER BY Fig_Num

Posted 5-Jul-23 5:36am

Philip B 2021

Updated 5-Jul-23 9:04am

v2

Add a Solution

Comments

Richard MacCutchan 5-Jul-23 11:46am

The numbers are actually character types so they will be sorted according to the character order. If you want to sort them as numbers then you will need to convert the numeric parts to their integer values.

3 solutions

Solution 1

Basically, don't.

Store dates as DATE, DATETIME, or DATETIME2 values, and convert to that in your presentation language before you store values in your DB. You presentation language is also responsible for deciding how to present the DB date values to the user.

Why? Surely that's more work?

Yes and no.
Yes, because you have to use a different datatype in your DB, and use a parameterised query to pass it to SQL (but if you aren't doing that anyway, you are going to lose your DB one day through SQL Injection).
Yes, because you have to validate and convert the entered date in your presentation language.

No, because you can now trust your date-based data: it's valid and genuine, which isn't the case for text based dates. What date is 01-02-03? First Feb 2003? Second Jan 2003? Or the third Feb 2001? Only your user who entered the date knows, and by the time a string based date arrives at your DB it has no connection to any specific user so is not trustable! Your database fills with "who knows?" dates and there is nothing you can do to find out which date they refer to.

No, because your current user when you present the date to them later may use a totally different dating system from the user that entered them. And you need to be aware of that or they may assume the wrong format and you get problems all over again. It's much, much easier to store Date based info in a "proper" format, and convert that to the current user's preferred format that is is to faff with string based ones - the system he is using will do it for you!

No, because you can compare DATE, DATETIME, and DATETIME2 values directly which string based comparisons are always resolved by comparing the first different pair of characters from two strings. So 10-Feb-2003 is "bigger" than 05-Jul-2023 as a string comparison because '1' is bigger than '0' - the rest of the strings isn't even looked at!

Always store data in the correct datatypes - it saves you enormous amounts of work later!

Posted 5-Jul-23 6:25am

OriginalGriff

Comments

Richard MacCutchan 5-Jul-23 12:26pm

Dates?

Philip B 2021 5-Jul-23 12:37pm

That was a typo. The subject line should have been "How to order varchar data by numeric char and then alphanumeric char"

Richard MacCutchan 5-Jul-23 12:52pm

Then please change it by using the Improve question link below your question.

Solution 2

You need a really UGLY order by statement. Something like this should work for your sample above (column name is a):

SQL

SELECT *
FROM (
  VALUES ('1'), ('2'), ('3'), ('8'), ('9'), ('10'), ('3A'), ('1B')
) AS X(a)

ORDER BY 
Cast(Reverse(substring(Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%', a ) , len(a) )))) , patindex('%[0-9]%', Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%', a) , len(a) )))) ), len(Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%', a) , len(a) ))))) )) as Int)
, SUBSTRING(a, Len(Reverse(substring(Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%', a ) , len(a) )))) , patindex('%[0-9]%', Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%',a) , len(a) )))) ), len(Reverse(rtrim(ltrim( substring(a , patindex('%[0-9]%', a) , len(a) ))))) ))) + 1  , 9999)

Posted 5-Jul-23 8:26am

dbrenth

Comments

Dave Kreskowiak 5-Jul-23 15:06pm

Performance sucks ass when you start doing manipulations during the query. It would be better to not have to do that at all. You can get away with multiple columns holding each part of the figure ID value and just do a simple ORDER BY field_firstPart, field_secondPart and great FAR greater performance out of the query.

dbrenth 5-Jul-23 15:11pm

I answered the question with the assumption that they didn't have the ability to alter the table. And also as a challenge to myself to see if I could actually do it in a single SQL statement. I agree it is far from ideal for performance and maintenance.

Philip B 2021 5-Jul-23 15:35pm

Thank you all for your knowledge and advice. I am unfortunately not able to modify the original table as there is already a large amount of data in it. I think I will try to reorder the data programmatically in C# using datatables or lists.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2023-07-05T09:04:00

You're representing your data in the database incorrectly. AVOID doing string manipulations during the query, like in Solution 2. That stuff will only slow down your query dramamtically.

Rethink how you're representing the figure ID value. Can you change that so you are using a format like ##.##, instead of using the alpha characters? If so, you can simply change the figures to floating point numbers and represent that column in the database as numeric, making the sort very easy.

Or, if you have to use the format of figure ID as it is, you can break the data into three columns, one with the full figure specification like you have now, and two more. One representing the numeric part of the ID, and another character column with the alpha characters. When you insert/update a record to the database, you have to break the figure IDs into their respective columns. What advantage do this give you? Far greater speed in your ORDER BY clause, just having to do something like this:

SQL

SELECT columns, Fig_FullId
FROM table
WHERE conditional
ORDER BY Fig_Numeric ASC, Fig_Alpha ASC

How to order varchar data by numeric char and then alphanumeric char

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us