• # Perform AlphaNumberic Sort or Order by in sql

hi evary one,
how to perform AlphaNumberic sorting or order by in sql.please check my below snopshot.
Thanks and Regards,
• Hi
you can go through below Query

Step1
`select * from a11 order by Area,Price`

Step2
`Select * From a11 ORDER BY Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20) When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20) Else ID End`

Step3
`Select *,Row_Number() Over (Order by Case When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20) When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20) Else IDEND) As RowNumber,ID From a11`

Step4

`SELECT * FROM a11ORDER BY LEFT(Area,PATINDEX('%[0-9]%',Area)-1), -- alphabetical sort CONVERT(INT,SUBSTRING(Area,PATINDEX('%[0-9]%',Area),LEN(Area))) -- numerical sort`

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

• Hi,
Try this scripts:
1. To order by Area:
select area,price from Table order by CONVERT(numeric,REPLACE(area,RIGHT(area,11),'')) desc
2. To order by Price:
select area,price from Table order by CONVERT(numeric,REPLACE(price,RIGHT(price,5),'')) asc

• Thank you both are working excellent

• when you directly trying to soft alpha numeric columns you can get 11 immediately after 1, the items aren't sorted to according to alphanumeric order. The reason for this is that SQL sorts character by character from left to right. In its mind, the correct alphabetical sequence is …B1, B2, B20, B21, B3,…
In the simple case the idea is to separate the alpha characters from the numeric, then sort by the alpha characters, convert the numeric portion to a number and sort.
see below link for more details
http://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

• Thankyou for responding.
i check all aboue code but i get one common error.
"Error converting data type nvarchar to float."
when i save the price like 2,000.
Thanks and Regards

• Hi

price like 2,000

see this line Comma is string value so we canot insert float datatype this data so error shows them

try to insert simple 2000 after you can format the value then we can shows decimal to comma

format numeric whether retrieve value

`// just two decimal placesString.Format("{0:0.00}", 123.4567); // "123.46"String.Format("{0:0.00}", 123.4); // "123.40"String.Format("{0:0.00}", 123.0); // "123.00"// max. two decimal placesString.Format("{0:0.##}", 123.4567); // "123.46"String.Format("{0:0.##}", 123.4); // "123.4"String.Format("{0:0.##}", 123.0); // "123"// at least two digits before decimal pointString.Format("{0:00.0}", 123.4567); // "123.5"String.Format("{0:00.0}", 23.4567); // "23.5"String.Format("{0:00.0}", 3.4567); // "03.5"String.Format("{0:00.0}", -3.4567); // "-03.5"String.Format("{0:0,0.0}", 12345.67); // "12,345.7"String.Format("{0:0,0}", 12345.67); // "12,346"String.Format("{0:0.0}", 0.0); // "0.0"String.Format("{0:0.#}", 0.0); // "0"String.Format("{0:#.0}", 0.0); // ".0"String.Format("{0:#.#}", 0.0); // ""String.Format("{0,10:0.0}", 123.4567); // " 123.5"String.Format("{0,-10:0.0}", 123.4567); // "123.5 "String.Format("{0,10:0.0}", -123.4567); // " -123.5"String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "String.Format("{0:0.00;minus 0.00;zero}", 123.4567); // "123.46"String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"String.Format("{0:my number is 0.0}", 12.3); // "my number is 12.3"String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"`

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

• First seperate alpha numerics in prices and area and convert them numeric and then sort it by order by. Write a userdefined functions for replacing commas and alphabetic part store it and convert the datatype.
SRI RAMA PHANI BHUSHAN KAMBHAMPATI