[thelist] Sorting with SQL
rudy
rudy937 at rogers.com
Thu Sep 25 12:24:56 CDT 2003
> The known patterns are:
>
> LLL N-N
> LLL NN-N
> LL N-N
> NLNN
> LLLLLL
> NNNN
>
> Would you then just extend the case statements?
yes
what the revised code below does is check for the blank,
and if there is one, it goes ahead with pulling out the
numerics based on the position of the dash (and note that
it *will* crash if it finds LLL NL-N or LLL LN-N)
but if there is no blank, it now checks to see if the
entire string contains a non-numeric character
if the entire string is all numerics (NNNN) then that's
the sort key used, but if there is a non-numeric, it pulls
out only the leading numerics
pretty neat, eh?
please don't come back and revise this once again, because
i'm afraid i'll have to say YOYO -- you're on your own
;o)
rudy
delete from strings
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 ( 'OK 1-1' )
insert into strings values ( 'OK 9-9' )
insert into strings values ( '2A10' )
insert into strings values ( '9X99' )
insert into strings values ( 'LETTERS' )
insert into strings values ( 'NUMBERS' )
insert into strings values ( '0937' )
insert into strings values ( '9370' )
select string
, cast(
case when charindex(' ',string) = 0
then case when string like '%[^0-9]%'
then
case when patindex('%[^0-9]%',string) > 1
then substring(string,1
,patindex('%[^0-9]%',string)-1)
else '0' end
else string end
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 case when string like '%[^0-9]%'
then
case when patindex('%[^0-9]%',string) > 1
then substring(string,1
,patindex('%[^0-9]%',string)-1)
else '0' end
else string end
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
string numericsortvalue
-------------------- ----------------
LETTERS 0
NUMBERS 0
ABC 1-2 1
OK 1-1 1
2A10 2
ABC 3-6 3
ABC 4-5 4
9X99 9
OK 9-9 9
ABC 10-3 10
0937 937
9370 9370
More information about the thelist
mailing list