Attention SQL Programmer's

I have an alphanumeric field that I need to sort. So for example the data
is:
1A
2A
10A
1B
11A
When I sort it needs to come out like:
1A
1B
2A
10A
11A
Can someone provide the best method on how to do this?
Thank you,
DavidSELECT your_column
FROM your_table
ORDER BY RIGHT('000' + your_column, 3)
Jacco Schalkwijk
SQL Server MVP
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:%23wMYh8vLFHA.3708@.TK2MSFTNGP14.phx.gbl...
>I have an alphanumeric field that I need to sort. So for example the data
>is:
> 1A
> 2A
> 10A
> 1B
> 11A
> When I sort it needs to come out like:
> 1A
> 1B
> 2A
> 10A
> 11A
> Can someone provide the best method on how to do this?
> Thank you,
> David
>|||The "best" solution is to divide this column up into two separate columns,
one numeric and one CHAR. Barring this schema change, other alternatives
might include:
1) split the data element up as it is read (should be easy if it's always of
the format nA or nnA where n is a numeric and A is an alphabetic char).
Just grab the RIGHT(sortvalue, 1) to get the CHAR and the rest
(SUBSTRING(sortvalue, 1, LENGTH(sortvalue) - 1) is the number - make sure to
convert to a numeric type).
2) A simpler solution might be to left pad with 0's, either in your DB or
during the select. For instance, to left pad with up to 3 zeros in the
select, you could do something similar to this:
SELECT sortvalue
FROM mytable
ORDER BY (RIGHT('000' + sortvalue, 3))
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:%23wMYh8vLFHA.3708@.TK2MSFTNGP14.phx.gbl...
>I have an alphanumeric field that I need to sort. So for example the data
>is:
> 1A
> 2A
> 10A
> 1B
> 11A
> When I sort it needs to come out like:
> 1A
> 1B
> 2A
> 10A
> 11A
> Can someone provide the best method on how to do this?
> Thank you,
> David
>|||Try,
use northwind
go
select
colA
from
(
select cast('1A' as varchar(25))
union all
select '2A'
union all
select '10A'
union all
select '1B'
union all
select '11A'
) as t(colA)
order by
cast(left(colA, patindex('%[^0-9]%', colA) - 1) as int) asc,
right(colA, patindex('%[0-9]%', reverse(colA)) - 1) asc
go
AMB
"David Pope" wrote:

> I have an alphanumeric field that I need to sort. So for example the data
> is:
> 1A
> 2A
> 10A
> 1B
> 11A
> When I sort it needs to come out like:
> 1A
> 1B
> 2A
> 10A
> 11A
> Can someone provide the best method on how to do this?
> Thank you,
> David
>
>|||Am I missing something because this doesn't produce the desired result?
David
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23i8yLAwLFHA.3016@.TK2MSFTNGP15.phx.gbl...
> SELECT your_column
> FROM your_table
> ORDER BY RIGHT('000' + your_column, 3)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:%23wMYh8vLFHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Where is it failing to produce the desired result? Do you have data items
that have more than three digits or more than one alphabetic character on
the end? Is this a VARCHAR column?
This is why you should post DDL as well as sample data. For the sample data
you provided, this should produce the expected result you provided.
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:Ovq1JGwLFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Am I missing something because this doesn't produce the desired result?
> David
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23i8yLAwLFHA.3016@.TK2MSFTNGP15.phx.gbl...
>|||And for further clarification of what Michael is asking for:
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> This is why you should post DDL as well as sample data. For the sample
data
> you provided, this should produce the expected result you provided.|||Here is the table script and the data being used is the exact data posted
earlier.
CREATE TABLE [dbo].[SortExample] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Michael C#" <xyz@.yomomma.com> wrote in message
news:eJEnLIwLFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Where is it failing to produce the desired result? Do you have data items
> that have more than three digits or more than one alphabetic character on
> the end? Is this a VARCHAR column?
> This is why you should post DDL as well as sample data. For the sample
> data you provided, this should produce the expected result you provided.
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:Ovq1JGwLFHA.3512@.TK2MSFTNGP15.phx.gbl...
>|||Here's your DML:
SELECT [name]
FROM SortExample
ORDER BY RIGHT('000' + [name], 3)
This assumes that the data is not Left or Right-padded with Spaces, however.
It also assumes that none of your [name] values will be over 3 characters
long, which is a safe assumption based solely on your sample data. To
compensate for any left or right padding add LTRIM() and RTRIM() like this:
SELECT [name]
FROM SortExample
ORDER BY RIGHT('000' + LTRIM(RTRIM([name])), 3)
Here's the results with your sample data:
1A
1B
2A
10A
11A
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:%23ZEfALwLFHA.2252@.TK2MSFTNGP15.phx.gbl...
> Here is the table script and the data being used is the exact data posted
> earlier.
>
> CREATE TABLE [dbo].[SortExample] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:eJEnLIwLFHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Thank you very much!
It works. Excellent!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23i8yLAwLFHA.3016@.TK2MSFTNGP15.phx.gbl...
> SELECT your_column
> FROM your_table
> ORDER BY RIGHT('000' + your_column, 3)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Pope" <dpope@.nospam.satx.rr.com> wrote in message
> news:%23wMYh8vLFHA.3708@.TK2MSFTNGP14.phx.gbl...
>