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,
You must Sign In to post a response.
Category: SQL Server
how to perform AlphaNumberic sorting or order by in sql.please check my below snopshot.
Thanks and Regards,
#766264
Hi
you can go through below Query
Step1
Step2
Step3
Step4
Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com
'Not by might nor by power, but by my Spirit,' says the LORD Almighty.
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 ID
END) As RowNumber,ID From a11
Step4
SELECT * FROM a11
ORDER 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.
#766266
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
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
#766270
Thank you both are working excellent
#766276
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]
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]
#766296
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.
please check it once again.
Thanks and Regards
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.
please check it once again.
Thanks and Regards
#766299
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
Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com
'Not by might nor by power, but by my Spirit,' says the LORD Almighty.
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 places
String.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 places
String.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 point
String.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.
#766943
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
SRI RAMA PHANI BHUSHAN KAMBHAMPATI
Return to Return to Discussion Forum