Order a character field numercially

I have a table with code and description columns. The code column is a CHAR
(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it i
s a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the
numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001,
015, 101, etc. The codes are alphanumeric in nature. There are hundreds of
codes, which makes it difficult for a user to scroll down to find the corre
ct code. Is there a way to
sort a character field in this manner? ThanksORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CH
AR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account t
he numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 1
01, etc. The codes are alphanumeric in nature. There are hundreds of codes, which
makes it difficult for a user to scroll down to find the correct code. Is there a w
ay
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it is a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to
sort a character field in this manner? Thanks
ORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)