[dba-SQLServer] sorting problem

Hi Francis:

Yes, my problem is a little bit more complex than my example suggests :)  
Your solution is perfect for the sample data I provided but I can't use it 
as a sorting solution since it is only tailored to support the sample data. 
For example, if I were to introduce the value "a45x1x" then I get an error.

However, one part of the solution I finding intriguing is the 
"ascii(right(the_value,1)) between 48 and 57" part because it allows the 
SELECTstatement to identify that it is a number.  So if a character in the 
string is a number, then perhaps something can be done to decrease the 
"sorting value" of the number so that letters come first.

Sincerely,
Billy


>From: Francis Harvey <HARVEYF1 at westat.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: "'dba-sqlserver at databaseadvisors.com'" 
><dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer] sorting problem
>Date: Sun, 31 Oct 2004 14:06:44 -0500
>
>Billy,
>
>Perhaps this code will give you some ideas to build on as I imagine
>your problem is more complex than your example suggests:
>
>select the_value
>from #the_table
>order by
>     case
>         when ascii(right(the_value,1)) between 48 and 57
>             then cast(substring(the_value,2,len(the_value) - 1) as int)
>         else cast(substring(the_value,2,len(the_value) - 2) as int)
>     end, the_value
>
>Francis R Harvey III
>WB 303, (301)294-3952
>harveyf1 at westat.com
>
>
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf
> > Of Billy Pang
> > Sent: Friday, October 29, 2004 8:35 PM
> > To: dba-SQLServer at databaseadvisors.com
> > Subject: [dba-SQLServer] sorting problem
> >
> >
> > 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 */
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>