Sorting with SQL

Question

Am newbie to SQL Server. In that i need to sort the following data

1, 1AB, 1AA, 20, 3C, 4D

into

1, 1AA, 1AB, 3C, 4D, 20.

My coding is of

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'getIntPortion')
 DROP FUNCTION getIntPortion
GO    
CREATE FUNCTION dbo.getIntPortion ( @inputVarchar varchar(255))
RETURNS VARCHAR(255)
AS
BEGIN
 WHILE(PATINDEX('%[^0-9]%', @inputVarchar) ) > 0
 BEGIN
 --then remove that one character, then continue
 SET @inputVarchar = REPLACE(@inputVarchar
 , SUBSTRING(@inputVarchar, PATINDEX('%[^0-9]%', @inputVarchar), 1)
 , '')
 END
 RETURN @inputVarchar
END

SELECT km_ph_act_chapt_no FROM [KM_DB].[dbo].[km_ph_act_chapters]
ORDER BY CONVERT(INT, dbo.getIntPortion(km_ph_act_chapt_no))

For this Alpha is not ordering... Hopefully waiting for reply.

Thanks and Regards,

Asker

Its sorting with NUmeric... But Not in the above order i have mentioned. — Asker
– Asker, Commented Aug 13, 2012 at 13:05
Can you explain the algorithm you're trying to sort by? Are you trying to sort by the numeric portion as a number, and then by alpha within a number? — Steven Mastandrea
– Steven Mastandrea, Commented Aug 13, 2012 at 13:07
If the rows had two columns, one for the int part, one for the varchar part, it would be easy — Jodrell
– Jodrell, Commented Aug 13, 2012 at 13:14
Jodrell: Actually i need to sort only by the chapter Number. So consider like.. Chapter 1, Chapter 1B, Chapter 2 etc..., — Asker
– Asker, Commented Aug 13, 2012 at 13:20

Rob Farley · Accepted Answer · 2012-08-13 13:11:34Z

3

Try:

ORDER BY CONVERT(INT, dbo.getIntPortion(km_ph_act_chapt_no)), km_ph_act_chapt_no;

But I would recommend against using a scalar function like this.

answered Aug 13, 2012 at 13:11

Rob Farley

15.9k5 gold badges46 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2012-08-13 13:10:44Z

2

Assuming your function works correctly, change the order by to

SELECT km_ph_act_chapt_no FROM [KM_DB].[dbo].[km_ph_act_chapters] 
ORDER BY CONVERT(INT, dbo.getIntPortion(km_ph_act_chapt_no)) ,
   km_ph_act_chapt_no

answered Aug 13, 2012 at 13:10

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

MakkyNZ · Accepted Answer · 2012-08-13 13:14:18Z

1

add a secondary sort so that it will sort the letters after you have sorted by the leading number

e.g

SELECT km_ph_act_chapt_no FROM [KM_DB].[dbo].[km_ph_act_chapters]
ORDER BY CONVERT(INT, dbo.getIntPortion(km_ph_act_chapt_no)), km_ph_act_chapt_no

answered Aug 13, 2012 at 13:14

MakkyNZ

2,2756 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Sorting with SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related