CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Asker
Asker

Reputation: 735

Sorting with SQL

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

Upvotes: 0

Views: 121

Answers (3)

MakkyNZ
MakkyNZ

Reputation: 2255

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

Upvotes: 1

Rob Farley
Rob Farley

Reputation: 15849

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.

Upvotes: 3

podiluska
podiluska

Reputation: 51494

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

Upvotes: 2

Related Questions