Re: SORTING PROBLEM WHEN NUMBERS ARE MIXED WITH LETTERS

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Fri, 21 Dec 2001 21:45:27 +0100
Message-ID: <3C239F67.8036792E@d2mail.de>

Hi Rob,

according to your sorting rules you should receive

60
61
62
63
136b
137
138
220
238
239
TESTRUN2
abc2
abc10
TESTRUN
TestRun
ab

Remember your last rule: "If the letter lines have numbers, they would proceed any lines that have ONLY letters."

The following sql statement achieves this sorting:

define maxlen = 40
set linesize 10000
set pagesize 0
set trimspool on

-- column sortpos1 noprint
-- column sortpos2 noprint
-- column sortpos3 noprint
select	name
	-- If the name starts with a digit, they are ordered first.
	, decode (
		sign ((ascii (substr (name, 1, 1)) - (ascii ('0') - 1)) * (ascii (substr


(name, 1, 1)) - (ascii ('9') + 1)))

		, - 1, 0	-- first letter is a digit
		, 1	-- first letter is not a digit
	) as sortpos1
	, decode (
		sign ((ascii (substr (name, 1, 1)) - (ascii ('0') - 1)) * (ascii (substr


(name, 1, 1)) - (ascii ('9') + 1)))

		, - 1, to_number (translate (
			utl_raw.cast_to_varchar2 (	-- conversion back to normal string
				utl_raw.bit_xor (	-- masking out
					utl_raw.cast_to_raw (translate (name, '0123456789', lpad (chr (0), 10, chr


(0))))	-- our mask

					, utl_raw.cast_to_raw (name)	-- the string to be masked
				)
			)
			, ' ' || chr (0)	-- our result of masking
			, ' '	-- our result of masking
		))
		, to_number (null)
	) as sortpos2
	, decode (
		translate (
			utl_raw.cast_to_varchar2 (	-- conversion back to normal string
				utl_raw.bit_xor (	-- masking out
					utl_raw.cast_to_raw (translate (name, '0123456789', lpad (chr (0), 10, chr


(0))))	-- our mask

					, utl_raw.cast_to_raw (name)	-- the string to be masked
				)
			)
			, 'x' || chr (0), 'x'
		)
		, null, 1	-- name does not contain digits. These come last.
		, 0	-- name contains digits. These come first
	) as sortpos3
	, to_number (translate (
		utl_raw.cast_to_varchar2 (	-- conversion back to normal string
			utl_raw.bit_xor (	-- masking out
				utl_raw.cast_to_raw (translate (name, '0123456789', lpad (chr (0), 10, chr


(0))))	-- our mask

				, utl_raw.cast_to_raw (name)	-- the string to be masked
			)
		)
		, ' ' || chr (0)	-- our result of masking
		, ' '	-- our result of masking
	)) as sortpos4
from	table1

order by sortpos1, sortpos2, sortpos3, sortpos4 /

maxlen is the maximum length name can be.

Regards,

Martin

Rob Earl wrote:

> 
> Recently I have been presented with a problem of figuring out how to
> display information from a table which has a VARCHAR2 field that can
> contain numbers as well as alphanumeric characters.  When just doing a
> simply ORDER BY the fields is sorted in an ASCII way in which numbers
> are out of numeric order and are in order of there ASCII
> representation.  I understand why the problem is occurring, but I'm
> stuck trying to find an easy way in SQL to sort these values properly.
>  To make things a little tricker, there can be a mixture of
> alphanumeric characters as well as numbers.  Here is some example data
> as seen with just a plain ORDER BY sort:
> 
> 136b
> 137
> 138
> 220
> 238
> 239
> 60
> 61
> 62
> 63
> TESTRUN
> TESTRUN2
> TestRun
> ab
> abc10
> abc2
> 
> Through searching the USENET I did manage to come up with an SQL
> statement that comes really really close to solving the problem:
> 
> select NAME, nvl( trim (trailing from translate( lpad(NAME,40,' '),
>  '1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
>  '1234567890' )  ), NAME) new_a
>  from table1
>  order by new_a;
> 
> That statement returns the following:
> 
> 60
> 61
> 62
> 63
> 137
> 138
> 220
> 238
> 239
> 136b
> abc2
> abc10
> TESTRUN2
> TESTRUN
> TestRun
> ab
> 
> This was really close, but what the statement works best with is if
> the field only contains JUST numbers or JUST letters.  If there is a
> mixture of both it kind of gets screwed up.  The line out of place is
> the 136b.  It really should be right before the 137 line.
> 
> This seems like it should be a real common problem, considering this
> type of data must exist in many systems.  Does anyone out there have
> any idea how I could possibly get my data to sort properly?  When I
> say properly, I mean that it would be sorted like the following:
> 
> 60
> 61
> 62
> 63
> 136b
> 137
> 138
> 220
> 238
> 239
> abc2
> abc10
> TESTRUN2
> TESTRUN
> TestRun
> ab
> 
> In a nutshell, this means that to be sorted properly all lines
> starting with a number would come first, followed by lines starting
> with letters.  If the letter lines have numbers, they would proceed
> any lines that have ONLY letters.  This seems like a fairly normal
> sort order to me.
> 
> Thanks in advance for any help.  I appreciate it greatly.
> 
> Rob

Received on Fri Dec 21 2001 - 14:45:27 CST