ordering alphanumeric fields

[monkeymeister]

How do you get alphanumberic fields to order themselves based on the numeric value? For example, I have a varchar field with values such as RAC1, RAC2, RAC3, RAC10 etc but when I do an order by I always get the order RAC1, RAC10, RAC2 etc. Do I need to cast the field as a numeric value and order by that?


Any help would be really appreciated.

Cheers,

Mike

 

[SQLDenis]

If the first 3 character are always varchar use this

create table zzz (field varchar(5))
insert into zzz
select 'RAC1'
union all
select 'RAC2'
union all
select 'RAC3'
union all
select 'RAC10'
union all
select 'RAC22'

select * from zzz
order by replace(field,left(field,3),'')

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

If the first 3 character are not always the same do this

select * from zzz
order by left(field,3)asc,replace(field,left(field,3),'')

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[monkeymeister]

SQLDenis,

This works unless I am using a UNION in my SQL (which unfortunately I am doing). This is the error I get :

ORDER BY items must appear in the select list if the statement contains a UNION operator

This is the relevant part of my SELECT:

select ISNULL(c.campaignname, 'RAC11'), COUNT(a.callResultKey) AS callresultcount, cl.CallResultDescription
from companysite c

and here is the ORDER BY :

order by replace(c.campaignname, left(c.campaignname,3),''), cl.CallResultDescription



Regards,

Mike

 

[SQLDenis]

select ccc.field from(
select isnull(field,'xxx99') as field from zzz
union all
select 'abc' as field) ccc
order by left(field,3)asc,replace(field,left(field,3),'')

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

You need to convert to int in the lat step

select ccc.field from(
select isnull(field,'xxx99') as field from zzz
union all
select 'abc' as field) ccc
order by left(field,3)asc,convert(int,replace(field,left(field,3),''))

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/