Issue with simple sorting. Should I fix at database or in code?

Question

I am working on a sort function for a table column that holds desk numbers and names.

This is a legacy program and was designed so that this column is nvarchar.

Because of this, the sort function cannot sort numerically as shown below:

enter image description here

Should I go into the database and alter this column to add leading zeros to number-only entries? Is this even do-able since the column is nvarchar?

Or should I add code at the object-level to add leading zeros just before the data is presented?

If you add them to the data it will display in your list. If you do it in your sort you are adding a lot of complexity for little benefit. — Sean Lange
– Sean Lange, Commented Jan 20, 2015 at 15:39
It is an honest question, why are you all voting him down without mentioning a reason or providing a duplicate link? We are here to help each other, why discourage the new members? We were all new once! — Jake Brewer
– Jake Brewer, Commented Jan 20, 2015 at 15:41
primarily opinion-based Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. — emerson.marini
– emerson.marini, Commented Jan 20, 2015 at 15:43

Giorgi Nakeuri · Accepted Answer · 2015-01-20 16:06:05Z

1

I would add one getter property in my class so it look like

public int Ordering
{
     get
     {
          return int.Parse(CharColumn);
     }
}

and when getting list of those objects simply order by that new property.

SQL Server is built upon SET Theory which states that, if you order your result set, you get non-relational data.

answered Jan 20, 2015 at 16:06

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:12:49Z

0

I would create another database column with the numeric value (there may be some columns that don't convert well, make sure you identify these and work to convert them). Then I would work to convert your application to use the new values. If the conversion is too great you could make sure all new development uses the new column and old code is migrated the next time someone touches it.

The risk to your approach is too fold, storing them in varchar is inefficient with both memory and processing power. Adding zeroes will help with the sorting but not fix the root issue.

Here is another question that I think will help you: SQL Server : error converting data type varchar to numeric

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Jan 20, 2015 at 15:39

Jake Brewer

2,6441 gold badge24 silver badges25 bronze badges

Collectives™ on Stack Overflow

Issue with simple sorting. Should I fix at database or in code?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related