locked
Sorting numbers and strings RRS feed

  • Question

  • Hi,

    I've a column in report builder with values such as : 70x500, 100x200, 150x200 and 80x200

    The above I will have sorted like:  70x500, 80x200, 100x200, 150x200   (right now they are sorted like 100x200, 150x200, 70x500, 80x200)

    How to achive this..?

    Thanks in advance.

    H

    Tuesday, December 16, 2014 12:40 PM

Answers

  • create table #t (id varchar(20))

    insert into #t values ('80x200')
    insert into #t values ('70x500')
    insert into #t values ('150x200')
    insert into #t values ('100x200')


    select * from #t
    order by cast(left(id,charindex('x',id)-1) as int)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 16, 2014 12:53 PM
  • Hi HCMJ,

    Based on my understanding, you want to sort the column by the values which behind the multiplication sign, right?

    As Uri mentioned, you can achieve your goal on query level. You can open Dataset Properties then type the query in Query Designer.

    Regarding JM’s suggestion, you just need change the compare character in the expression. As we tested in our environment, we set the sort expression as below:

    =CInt(Left(Fields!id.Value,InStr(Fields!id.Value,"x")-1))
    Then we get expected results.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu

    Thursday, December 18, 2014 12:39 PM

All replies

  • create table #t (id varchar(20))

    insert into #t values ('80x200')
    insert into #t values ('70x500')
    insert into #t values ('150x200')
    insert into #t values ('100x200')


    select * from #t
    order by cast(left(id,charindex('x',id)-1) as int)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 16, 2014 12:53 PM
  • Hello:

    Not is necesary create a temporal table to sort this:

    You can use the FX in "Order by" option  into your tablix and use:

    =CInt(Left(YourData.Value,(InStr(YourData.Value,"-") -1))


    JM Claudio Dba/Consultor SQL/BI Pasiona - Spain

    • Proposed as answer by JM Claudio Tuesday, December 16, 2014 2:04 PM
    Tuesday, December 16, 2014 2:04 PM
  • Thanks for the suggestion, I'm though having the below error when executing the report: "Input string was not in the correct format" Any idea?
    Tuesday, December 16, 2014 3:10 PM
  • Sorry

    =CInt(Left(Fields!Tipo.Value,InStr(Fields!Tipo.Value,"-")-1))


    JM Claudio Dba/Consultor SQL/BI Pasiona - Spain

    Tuesday, December 16, 2014 4:07 PM
  • Thanks Again, but I'm still seeing the same issue, could it have something to do with that there could be data with longer string than 70x500 - for example 70x500x450 ?
    Tuesday, December 16, 2014 7:29 PM
  • Ok, you can use.

    CInt(Replace(Fields!Tipo.Value,"-","")) and now 

    70x500x450 = 70500450

    100x500x450=100500450

    Best Regards.


    JM Claudio Dba/Consultor SQL/BI Pasiona - Spain

    Tuesday, December 16, 2014 8:32 PM
  • Hi HCMJ,

    Based on my understanding, you want to sort the column by the values which behind the multiplication sign, right?

    As Uri mentioned, you can achieve your goal on query level. You can open Dataset Properties then type the query in Query Designer.

    Regarding JM’s suggestion, you just need change the compare character in the expression. As we tested in our environment, we set the sort expression as below:

    =CInt(Left(Fields!id.Value,InStr(Fields!id.Value,"x")-1))
    Then we get expected results.

    If you have any question, please feel free to ask.

    Best regards,
    Qiuyun Yu

    Thursday, December 18, 2014 12:39 PM
  • I've just discovered that my data is not always starting with a numeric value (like 100x200) there are cases where it starts with a string like k500x100 or just a string value like "Big Box"

    Is there any way of checking this using "IIF" saying that is should sort as you suggested in previous example(which is working) and if it starts with a string value then it's not sorted or the sorting is ignored ?

    Right now if I run your suggested solution it's fails if there is no "x" I guess...

    Any suggestions?

    Thursday, January 8, 2015 2:10 PM