locked
SQL Order by nvarchar but numeric RRS feed

  • Question

  • User1444011213 posted

    Hi,

    I have mssql statement:

    SELECT PRICE 
    FROM tblPrices
    ORDER BY PRICE

    PRICE is stored in db as nvarcharter so I when I get resoults they are not numericly order but for example I get:
    10,00
    103,00
    20,00

    End I need it to be numeric:
    10,00
    20,00
    103,00

    Any idea?

    Thx in advance

    Sunday, September 18, 2016 8:33 AM

Answers

  • User1577371250 posted

    Hi,

    try this

    SELECT PRICE FROM tblPrices ORDER BY CAST(PRICE AS INT) ASC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 18, 2016 2:08 PM
  • User2117486576 posted

    @Lokesh B R

    Might need to cast to decimal or a type with more precision than int.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 18, 2016 5:51 PM

All replies

  • User1577371250 posted

    Hi,

    try this

    SELECT PRICE FROM tblPrices ORDER BY CAST(PRICE AS INT) ASC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 18, 2016 2:08 PM
  • User2117486576 posted

    @Lokesh B R

    Might need to cast to decimal or a type with more precision than int.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 18, 2016 5:51 PM
  • User-158764254 posted

    Combining the replies from Lokesh and Richard, you should have your work-around.

    This does beg the question though...

    westgatezagreb

    PRICE is stored in db as nvarchar

    Why?  Why are you storing a Price which is fundamentally a numeric piece of data as textual (NVARCHAR) data?

    Trying to imagine how many more times in your application you are going to bump into Price being of the wrong datatype...

    I'd correct the root cause and alter the table column type.  maybe use Decimal(18,2)

    Sunday, September 18, 2016 7:27 PM
  • User-1404113929 posted

    hi,

    can you try with bellow code.

    declare @dt table(
    price nvarchar(50)
    )
    insert into @dt values('10,00')
    insert into @dt values('103,00')
    insert into @dt values('20,00')
    SELECT PRICE ,
    CHARINDEX(',',price)-1 as SORT_ORD
    into #temp
    FROM @dt
    select * from #temp
    ORDER BY SORT_ORD

    Thanks,

    Murali

    Monday, September 19, 2016 10:13 AM
  • User-1404113929 posted

    hi,

    try bellow code.

    declare @dt table(
    price nvarchar(50)
    )
    insert into @dt values('10,00')
    insert into @dt values('103,00')
    insert into @dt values('20,00')
    SELECT PRICE FROM @dt ORDER BY CAST(REPLACE(price,',','.') AS decimal) ASC

    thanks,

    murali

    Monday, September 19, 2016 10:37 AM