Sorting mixed type fields...

[Ray Lavelle]

I have a field in my database which stores product codes. This field
is a varchar. Some users use characters in their codes but most just
use numbers. Whenever I go to sort the products by code the order of
the numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...
I understand why this is sorted this way but is there anyway I can
have it sort the numbers in their proper numeric order (1,2,3,100,
....) and just have them always appear before any codes that contain
characters? Right now I just have users put zeros in front of their
numbers like: 0001, 0100, 0101, 0002, ...

[Rich Dillon]

Ray,

CREATE TABLE foo (c1 varchar(10));
INSERT INTO foo VALUES ('1');
INSERT INTO foo VALUES ('2');
INSERT INTO foo VALUES ('13');
INSERT INTO foo VALUES ('1X');

SELECT *
FROM foo
ORDER BY RIGHT('0000000000'+c1,10);

Hope that helps,
Rich
"Ray Lavelle" <bo************@yahoo.com> wrote in message
news:d8**************************@posting.google.c om...

I have a field in my database which stores product codes. This field
is a varchar. Some users use characters in their codes but most just
use numbers. Whenever I go to sort the products by code the order of
the numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...
I understand why this is sorted this way but is there anyway I can
have it sort the numbers in their proper numeric order (1,2,3,100,
...) and just have them always appear before any codes that contain
characters? Right now I just have users put zeros in front of their
numbers like: 0001, 0100, 0101, 0002, ...

[Hatheway, Darwin]

Do you care how the strings containing alphas are sorted? If not, you could
do something like this:

create table foo (col1 char(5))
go
insert foo values ('x')
insert foo values ('01')
insert foo values ('ax')
insert foo values ('2')
insert foo values ('0034')
insert foo values ('034')
insert foo values ('03')

select col1
from foo
order by
case when col1 like '%[a-z]%' then 99999
else convert(int, col1)
end

If you care how the alphas are ordered, too, I think you'll want to create a
function that handles zero-extending anyhthing convertible to numerics and
returns a char(x) type. You 'order by' on your function output. This
seemed to work for me (algorithm is ugly but it did the job):

create function orderit (@a_col char(5)) returns char(5)
as
begin
declare @num_scrap int
declare @char_scrap char(5)
declare @temp_len int
if (@a_col like '%[a-z]')
begin
set @char_scrap = @a_col
end
else
begin
set @num_scrap = convert(int,@a_col)
set @char_scrap = convert(char(5),@num_scrap)
set @temp_len = datalength(rtrim(@char_scrap))
set @char_scrap = replicate('0',5-@temp_len)+rtrim(@char_scrap)
end
return (@char_scrap)
end
go

select col1,
<mydb>.dbo.orderit(col1)
from foo
order by <mydb>.dbo.orderit(col1)

"Ray Lavelle" <bo************@yahoo.com> wrote in message
news:d8**************************@posting.google.c om...

I have a field in my database which stores product codes. This field
is a varchar. Some users use characters in their codes but most just
use numbers. Whenever I go to sort the products by code the order of
the numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...
I understand why this is sorted this way but is there anyway I can
have it sort the numbers in their proper numeric order (1,2,3,100,
...) and just have them always appear before any codes that contain
characters? Right now I just have users put zeros in front of their
numbers like: 0001, 0100, 0101, 0002, ...

[--CELKO--]

>> I have a field [sic] in my database which stores product codes.
This field [sic] is a varchar. <<

That is the root of your problem; you don't know that a column is not
a field (row is not a record, table is not a file), so you did not
bother to define the domain and put a CHECK() constraint to enforce
the rules for these codes. Using VARCHAR(n) is also a mistake for
encoding schemes 95% of the time -- it messes up checking, printing
forms, etc.

Right now I just have users put zeros in front of their numbers

like: 0001, 0100, 0101, 0002, ... <<

I don't know why you cannot use GTIN, EAN, UPC or some other industry
standard code. It sounds like the "database designer" did no research
or planning; can you fix your system and fire him?

And, yes, that padding is what you have to do and you'd better enforce
it in the DDL, not in every query that has been and ever shall be
written against the Database. Start by cleaning up the entire
database.

Based on the specs I have to invent myself since you did not give any,
your table should have a column something like this:

CREATE TABLE Products
(product_code CHAR(10) NOT NULL PRIMARY KEY
CHECK (product_code LIKE '[0-9][0-9][0-9][0-9][A-Z][A-Z]'),
...);

The Regular expression extension in SQL Server is a bit weaker than
the SIMILAR TO predicate in SQL-92, but it is quite usable.

[Ray Lavelle]

This worked perfect, thanks!

"Rich Dillon" <ri********@no.spam> wrote in message news:<gL***************@newsread4.news.pas.earthli nk.net>...

Ray,

CREATE TABLE foo (c1 varchar(10));
INSERT INTO foo VALUES ('1');
INSERT INTO foo VALUES ('2');
INSERT INTO foo VALUES ('13');
INSERT INTO foo VALUES ('1X');

SELECT *
FROM foo
ORDER BY RIGHT('0000000000'+c1,10);

Hope that helps,
Rich
"Ray Lavelle" <bo************@yahoo.com> wrote in message
news:d8**************************@posting.google.c om...

I have a field in my database which stores product codes. This field
is a varchar. Some users use characters in their codes but most just
use numbers. Whenever I go to sort the products by code the order of
the numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...
I understand why this is sorted this way but is there anyway I can
have it sort the numbers in their proper numeric order (1,2,3,100,
...) and just have them always appear before any codes that contain
characters? Right now I just have users put zeros in front of their
numbers like: 0001, 0100, 0101, 0002, ...