Sort numerically from an alphanumeric column

Question

Post reply

Sort numerically from an alphanumeric column

jatighe

SSChasing Mays

Points: 638
More actions
August 3, 2005 at 11:11 am

#89327

I am trying to sort numerically on an alphanumeric column in a table. The SQL I have used works well provided the sort column has a number in it. If there are no numbers then the SQL statement will fail with a
'Server: Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.'
error message. Any ideas on how to overcome this? An example of the data I am trying to sort is below:
SOR_CODE SOR_DESCRIPTION
~~~~~~~~ ~~~~~~~~~~~~~~~
AS4000WA Extra for removal of W.C. cistern as asbestos waste.
AZ4010WA Extra for removal of vinyl/linoleum kitchen floor covering as asbestos waste.
AS4020WA Extra for removal of vinyl/linoleum bathroom floor covering as asbestos waste.
AT4001WA Extra for removal of fuseboard as asbestos waste.
AS4040WA Extra for removal of Artex ceiling to kitchen as asbestos waste.
The data would be sorted using my SQL statement as:
AS4000WA
AT4001WA
AZ4010WA
AS4020WA
AS4040WA
However, if I had a row such as
SOR_CODE SOR_DESCRIPTION
~~~~~~~~ ~~~~~~~~~~~~~~~
MANUAL Manual Labour
the SQL would stop at this row, returning the above error
The SQL I am using is:
SELECT TOP 100 PERCENT
SOR_CODE,
SOR_DESCRIPTION
FROM SOR_SCHEDULE_OF_RATES
ORDER BY
SUBSTRING(SOR_CODE,PATINDEX('%[0-9]%', SOR_CODE),
LEN(SOR_CODE) - (LEN(RIGHT(SOR_CODE,PATINDEX('%[0-9]%', SOR_CODE) - 1)) +
LEN(LEFT(SOR_CODE, PATINDEX('%[0-9]%', SOR_CODE) - 1))))
Any ideas on how to overcome this or even a better solution?
Thanks
Justin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

VladRUS.ca SSC Enthusiast Points: 127 More actions · Answer 1

Hi jatighe,

If you use SQL 2000 try this code:

create function fn_GetOnlyNumbers

(

@string varchar(38)

)

returns numeric(38)

as

begin

declare @numeric numeric(38)

while patindex('%[^0-9]%', @string) > 0

set @string = STUFF(@string, PATINDEX('%[^0-9]%', @string), 1, '')

set @numeric = '0' + @string

return @numeric

END

go

SELECT TOP 100 PERCENT

SOR_CODE,

SOR_DESCRIPTION

FROM SOR_SCHEDULE_OF_RATES

ORDER BY dbo.fn_GetOnlyNumbers(SOR_CODE)

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

I think you should use VladRUS.ca's solution. But, just in case you want another approach that is FAR less efficient...

CREATE TABLE #TEST( SOR_CODE varchar(8))

INSERT INTO #TEST VALUES( 'AS4000WA')

INSERT INTO #TEST VALUES( 'AT4001WA')

INSERT INTO #TEST VALUES( 'AZ4010WA')

INSERT INTO #TEST VALUES( 'AS4020WA')

INSERT INTO #TEST VALUES( 'AS4040WA')

INSERT INTO #TEST VALUES( 'MANUAL')

SELECT SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE)

FROM #TEST

ORDER BY

CASE

WHEN PATINDEX( '%[0-9]%', SOR_CODE) - 1 = ( -1)

THEN SUBSTRING( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE),

LEN( SOR_CODE) - (LEN( RIGHT( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE))) +

LEN( LEFT( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE)))))

ELSE SUBSTRING( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE),

LEN( SOR_CODE) - (LEN( RIGHT( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE) - 1)) +

LEN( LEFT( SOR_CODE, PATINDEX( '%[0-9]%', SOR_CODE) - 1))))

END

DROP TABLE #TEST

I wasn't born stupid - I had to study.

jatighe SSChasing Mays Points: 638 More actions · Answer 3

Thanks for the response - I used the function and that worked perfectly

Regards

Justin

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Just for fun

ISNULL(STUFF(STUFF(SOR_CODE,PATINDEX('%[0-9][a-z]%',SOR_CODE)+1,LEN(SOR_CODE),''),1,PATINDEX('%[0-9]%',SOR_CODE)-1,''),'')

Far away is close at hand in the images of elsewhere.
Anon.