Sorting String Values

[John Smith]

Hello all:

New problem. I am currently storing some mostly numeric values as ids
in a db but with the possiblity that there are revisions to each. For
instance say we have a record such as 100, a revision may need to be
added such as 100a. As a result this column in the db must be set to a
string data type. However, when I do so the sort order gets whacked.
We have some records in the hundreds and they show up after those in the
thousands as a result of string sorting. For example, record 500 is
less than 1000 and so shows up below. However, we need it to sort as
though it were sorting integers but with the added requirement of
allowing for the revisions. Is this possible or should I come up with a
different scheme as far as revisions go such as tack on the revision tag
later in processing and not store it in the db (this is undesirable).

If you made it this far, thanks for reading ,

John

 

[Guest]

If it works for the rest of the application you could always store a fixed
number of digits.

For example, always store 6 digits so you would have 000100, 000500, 001000

Then on a change 000100a would not change the sort order.

John

 

[Bjorn Abelli]

...

New problem. I am currently storing some mostly numeric values as ids in
a db but with the possiblity that there are revisions to each. For
instance say we have a record such as 100, a revision may need to be added
such as 100a. As a result this column in the db must be set to a string
data type. However, when I do so the sort order gets whacked. We have
some records in the hundreds and they show up after those in the thousands
as a result of string sorting. For example, record 500 is less than 1000
and so shows up below. However, we need it to sort as though it were
sorting integers but with the added requirement of allowing for the
revisions. Is this possible or should I come up with a different scheme
as far as revisions go such as tack on the revision tag later in
processing and not store it in the db (this is undesirable).

If you made it this far, thanks for reading ,

I don't see this as a C# problem, but rather a DB problem.

I would go for a solution that makes it possible to "sort" the table
directly from the database (with an "ORDER BY" clause).

This means that my solution would be something like this:

CREATE TABLE humhum
(
id numeric,
revision char(1) DEFAULT '0',
...,

CONSTRAINT humhum_pk PRIMARY KEY (id, revision)
)

....then it's simple to make a select ... order by id, revision.

// Bjorn A

 

[MuZZy]

...




I don't see this as a C# problem, but rather a DB problem.

I would go for a solution that makes it possible to "sort" the table
directly from the database (with an "ORDER BY" clause).

This means that my solution would be something like this:

CREATE TABLE humhum
(
id numeric,
revision char(1) DEFAULT '0',
...,

CONSTRAINT humhum_pk PRIMARY KEY (id, revision)
)

...then it's simple to make a select ... order by id, revision.

// Bjorn A

Or if you can't change the db stucture for any reason, sort by int part of the column values, which
may be slow though. I would vote for Bjorn's solution