October 29, 2004 at 6:51 pm
Hello:
I have a table that contains one column with the following six values:
a1
a2
a21
a10x
a2a
a10
Currently if that column is sorted, it is sorted this way:
a1
a10
a10x
a2
a21
a2a
But I want it to be sorted this way:
a1
a2
a2a
a10
a10x
a21
(note: a2a comes before a10, a21 goes after a10x, etc...ergo, letters come before numbers, contrary to their ascii values)
Is it possible or is it a pipe dream? Below is copy of code.
Thanks in advance,
Billy
/* cut here */
use tempdb
create table #the_table(the_value varchar(10));
insert into #the_table(the_value) values('a1');
insert into #the_table(the_value) values('a2');
insert into #the_table(the_value) values('a21');
insert into #the_table(the_value) values('a10x');
insert into #the_table(the_value) values('a2a');
insert into #the_table(the_value) values('a10');
select * from #the_table order by the_value
drop table #the_table;
/* cut here */
October 30, 2004 at 1:41 pm
One possible way would be
set nocount on
/* cut here */
use tempdb
create table #the_table(the_value varchar(10));
insert into #the_table(the_value) values('a1');
insert into #the_table(the_value) values('a2');
insert into #the_table(the_value) values('a21');
insert into #the_table(the_value) values('a10x');
insert into #the_table(the_value) values('a2a');
insert into #the_table(the_value) values('a10');
select
* from
#the_table
order by
convert(int, replace (replace(the_value, 'a', ''), 'x' ,''))
drop table #the_table;
/* cut here */
set nocount off
the_value
----------
a1
a2
a2a
a10
a10x
a21
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 31, 2004 at 6:05 pm
Another opiton if the letters are not always 'a' and 'x'
select * from #the_table
order by replicate('0',10 - len(the_value) + patindex('%[0-9]%',reverse(the_value))) + the_value
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply