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, Jan 20 '15 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! — Jacob Brewer, Jan 20 '15 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, Jan 20 '15 at 15:43

score 1 · Answer 1 · answered Jan 20 '15 at 16:06

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.

score 0 · Answer 2 · edited May 23 '17 at 12:12

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

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

2 Answers2