Alphanumeric Value Sorting

Question

Post reply

Alphanumeric Value Sorting

realyeti

SSC Journeyman

Points: 91
More actions
March 10, 2011 at 2:53 am

#235923

i have different values like:
1
12
45
78
7a
9ce
a458
x78
I need to sort them by:
1
7a
9ce
12
45
78
a458
x78
My start query like:
select * from table where field like 'String' order by case
when isnumeric(field )=1 then
convert(float,field )
else 99999999999
end,field

Viewing 9 posts - 1 through 9 (of 9 total)

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

realyeti SSC Journeyman Points: 91 More actions · Answer 1

Sorry, i forgot some additional informations. This query returns the result:

1

12

45

78

7a

9ce

a458

x78

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Take a look at the first link in my signature line below. It may be why it's taking so long for someone to help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

realyeti SSC Journeyman Points: 91 More actions · Answer 3

Ok sorry:

CREATE TABLE [dbo].[Numbers](

[nvarchar](20) NULL

) ON [PRIMARY]

GO

INSERT INTO Numbers values('a458'),('x78'),('1'),('12'),('45'),('7a'),('78'),('9ce'),('100'),('1a')

select * from Numbers order by case

when isnumeric(code )=1 then

convert(float,code )

else 99999999999

end,code

I´m searching for the result:

1

1a

7a

9ce

12

45

78

100

a458

x78

Many thx for your help

Lowell SSC Guru Points: 323547 More actions · Answer 4

there may be a more efficient stripNonNumeric function, but this works:

select *

from Numbers

order by CONVERT(float, dbo.StripNonNumeric(code)),

code

/*--results

1

1a

7a

9ce

12

45

78

x78

100

a458

*/

and the function i used:

CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;WITH tally (N) as

(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

CASE

--ascii numbers are 48(for '0') thru 57 (for '9')

WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END

FROM tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

realyeti SSC Journeyman Points: 91 More actions · Answer 5

Sorry, but the result is not ok.

/*--results

1

1a

7a

9ce

12

45

78

x78

100

a458

*/

i need:

/*--results

1

1a

7a

9ce

12

45

78

100

a458

x78

*/

Start with number - sort alphabetical at the rest.

Thank you for your Help!

Lowell SSC Guru Points: 323547 More actions · Answer 6

i think you need to just change the order by then;

order by case when isnumeric([first char], then the rest of what i suggested.

besides what I posted, what have you tried that comes close?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

The following code returns what you are looking for based on the sample data provided. Please let us know if it works for you in your particular application.

select

code

from

dbo.Numbers

order by

cast(

case when patindex('%[A_Za-z]%',code) = 1 then '99999999999'

when patindex('%[A_Za-z]%',code) = 0 then code

else substring(code, 1, patindex('%[A_Za-z]%',code) - 1)

end as bigint),

code

;

realyeti SSC Journeyman Points: 91 More actions · Answer 8

Fantastic - that is exactly what i´m searching for!

Many, many thank´s !!