[thelist] Sorting with SQL
rudy
rudy937 at rogers.com
Wed Sep 24 14:19:08 CDT 2003
> I've not had a chance to explore the finer details
> of using SUBSTRING and CHARINDEX yet.
mmmm, i love string functions
here ya go, rob
create table strings
( string varchar(20) not null )
insert into strings values ( 'ABC 1-2' )
insert into strings values ( 'ABC 3-6' )
insert into strings values ( 'ABC 4-5' )
insert into strings values ( 'ABC 10-3' )
insert into strings values ( 'ABC 2' )
insert into strings values ( 'ABC 22' )
insert into strings values ( 'ABC 222' )
insert into strings values ( 'ABC 2-2-2' )
insert into strings values ( 'ABC 2-22-222' )
insert into strings values ( 'ABCXYZ 937' )
insert into strings values ( 'ABCXYZ 937-eh' )
insert into strings values ( 'ABC' )
insert into strings values ( 'AB' )
insert into strings values ( 'A' )
select string
, cast(
case when charindex(' ',string) = 0
then '0'
else substring(
string
, charindex(' ',string)+1
, case when charindex('-',string) = 0
then len(string)
- charindex(' ',string)
else charindex('-',string)
- charindex(' ',string) - 1
end )
end as smallint ) as numericsortvalue
from strings
order
by cast(
case when charindex(' ',string) = 0
then '0'
else substring(
string
, charindex(' ',string)+1
, case when charindex('-',string) = 0
then len(string)
- charindex(' ',string)
else charindex('-',string)
- charindex(' ',string) - 1
end )
end as smallint )
string numericsortvalue
-------------------- ----------------
ABC 0
AB 0
A 0
ABC 1-2 1
ABC 2 2
ABC 2-2-2 2
ABC 2-22-222 2
ABC 3-6 3
ABC 4-5 4
ABC 10-3 10
ABC 22 22
ABC 222 222
ABCXYZ 937 937
ABCXYZ 937-eh 937
by the way, i totally endorse what ken suggested --
alter the table and add a separate permanent column for sorting
rudy
p.s.
20 years database programming, ken? whoa
what was your first database? mine was idms in 1975...
More information about the thelist
mailing list