You must Sign In to post a response.
  • Category: SQL Server

    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,
  • #766264
    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 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

  • #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]

  • #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

  • #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


    // 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


Sign In to post your comments