Sort Alphabetically then Numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sort Alphabetically then Numeric

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-01-28 : 19:12:58

Gurus,
I am having trouble sorting an alphanumeric column in a table.

Here is what data looks like when I user ORDER BY clause in SQL Server...

5
A
B
BB
C

But I would like to get this result...

A
B
BB
C
5

Any suggestions...

Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-28 : 19:30:19

you need to stuff it with ' ' to get what you want. Also you haev to consider will the length ever change? is the maximum length of that value 2? otherwise you will still need to stuff but stuff it with the widest length of value of that field. Also what data type if that field?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

kellog1
Starting Member

35 Posts

Posted - 2010-01-28 : 20:16:34

Can you please elaborate on stuff more...the data type is char and max length is 2.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-29 : 02:23:57

try

order by len(col),col

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 03:51:51

Isn't this easier? Or am I missing something?


ORDER BY CASE WHEN MyColumn LIKE '[0-9]%' THEN 2 ELSE 1 END, MyColumn

nitookatyal
Starting Member

4 Posts

Posted - 2010-01-29 : 05:06:30

Hi,
May this will help u

order by
(stuff(col,PATINDEX('%[0-9]%',col),1,'')),(stuff(col,PATINDEX('%[A-Z]%[a-z]%',col),1,''))

Nitoo katyal
Software Engineer

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 05:12:53

What's that going to do Nitoo? I can't get my head around it ...

Sachin.Nand

2937 Posts

Posted - 2010-01-29 : 09:11:35

declare @tbl as table(val varchar(50))
insert into @tbl
select 'A' union all
select 'B' union all
select '15' union all
select 'BB' union all
select 'C' union all
select '25'

select * from @tbl
order by isnumeric(val),val

PBUH

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:33:01

Yeah, that's probably better than testing first character for [0-9].

@kellog1: How should "123ABC" sort - is that "numeric" and therefore last, or "alphanumeric" and therefore first?

Slight problem with IsNumeric() is that it will see "123E456" as being a number. Are all your "numbers" integers in this data set?

kellog1
Starting Member

35 Posts

Posted - 2010-01-29 : 11:21:47

"123ABC" should be sorted in alphanumeric fashion...e.g: 123ABC --> ABC123.

Sachin.Nand

2937 Posts

Posted - 2010-01-29 : 11:52:41

declare @tbl as table(val varchar(50))
insert into @tbl
select 'A' union all
select 'B' union all
select '15' union all
select 'BB' union all
select 'C' union all
select 'ABC123' union all
select '123ABC'

select * from @tbl
order by isnumeric(val)desc,val

PBUH

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 12:07:04

"order by isnumeric(val)desc,val"

You were right the first time with ASCENDING order on IsNumeric() I think?

Kellog1 didn't say if all numbers would be "integers", but if so I would use:


ORDER BY CASE WHEN LTrim(RTrim(MyColumn)) NOT LIKE '%[^0-9]%' THEN 2 ELSE 1 END, MyColumn

rather than IsNumeric() because that will treat anything that looks like floating point, or scientific notation, as a Number.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources