Numeric sorting SQL server table by column starting with number and string

Question

I'm going to sort an SQL server table by a column having following data:

2.5.1 Sonstiges
1.1.1 Pflegstandards
5.1.7 Arbeitsgemeinschaften
1.2.1 Anforderungen
2.4.5 Betriebsarzt
B Kernprozesse
1.1.1.4 Umgang mit
2.3.3 Kardiologie
......

as you see most of records begin with a number, but there are some starting with string as well. I use following query to cover both cases:

SELECT * from DocumentCategories 
  order by
    case IsNumeric(replace( LEFT(name, CHARINDEX(' ', name)),'.','')) 
        when 0 then name
        when 1 then cast(replace( LEFT(name, CHARINDEX(' ', name)),'.','') as int)
    end

But I get an error regarding varchar to int conversion (because of record containg "B Kernprozesse"). So what's the role of case/when here? Have I missed anything?

I want the output to look something like below:

B Kernprozesse
1.1.1 Pflegstandards
1.2.1 Anforderungen
2.3.3 Kardiologie
2.4.5 Betriebsarzt
2.5.1 Sonstiges
5.1.7 Arbeitsgemeinschaften
1.1.1.4 Umgang mit

......

Do not use ISNUMERIC() it has some issues. Does B Kernprozesse can be like B Kernprozesse1.2.3? — Ilyes
– Ilyes, Commented Aug 5, 2019 at 17:37
ISNUMERIC is a bad function, if I'm honest. If you really want to test if a string is a numerical value, use TRY_CONVERT or TRY_CAST. — Thom A
– Thom A ♦, Commented Aug 5, 2019 at 17:37
Also, the problem here is that you are returning 2 different datatypes from your CASE expression. [name] is clearly a varchar, but your other expression is being cast to an int. As a result [name] is cast to an int as well, due to data type precendence and so the conversion fails. — Thom A
– Thom A ♦, Commented Aug 5, 2019 at 17:38
What is your expected output? please add sample output data. — mkRabbani
– mkRabbani, Commented Aug 5, 2019 at 17:39
How does 1.1.1.4 compare to 1.1.2? Right now, your replace turns this into 1114 vs 112. Expected output would be helpful. — dvo
– dvo, Commented Aug 5, 2019 at 17:42

dvo · Accepted Answer · 2019-08-05 17:51:30Z

2

As mentioned in the comments, you can use TRY_CAST like so:

SELECT * FROM @DocumentCategories 
  ORDER BY
    CASE WHEN TRY_CAST(replace( LEFT(name, CHARINDEX(' ', name)),'.','') AS INT) IS NULL
        THEN 0 
        ELSE cast(replace( LEFT(name, CHARINDEX(' ', name)),'.','') as int) 
    END

If you are in a SQL_SERVER enviornment prior to version 2012, you can use ISNUMERIC and get the same results:

SELECT * FROM @DocumentCategories 
  ORDER BY
    CASE WHEN ISNUMERIC(replace( LEFT(name, CHARINDEX(' ', name)),'.','')) = 0 
        THEN 0 
        ELSE cast(replace( LEFT(name, CHARINDEX(' ', name)),'.','') as int) 
    END

Here are the results in both cases:

name
=====================
B Kernprozesse
1.1.1 Pflegstandards
1.2.1 Anforderungen
2.3.3 Kardiologie
2.4.5 Betriebsarzt
2.5.1 Sonstiges
5.1.7 Arbeitsgemeinschaften
1.1.1.4 Umgang mit

answered Aug 5, 2019 at 17:51

dvo

2,1532 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ali_dotNet Over a year ago

thanks, but what if I have a few more records starting with string? how can I have them sorted also? what does this 0 after then mean here?

dvo Over a year ago

@Ali_dotNet You can not easily sort numeric and alpha together. The 0 means if the CASE is true then use the provided value (0 in this case). 0 is used because it is lower than the other possible integers - If the CASE is true (name is not numeric / can not be cast), then put it at the top of the list. What this does is it assigns all non-numeric values to 0 and then the ORDER BY compares 0 and 0 and declares a tie. Hopefully that makes sense.

Ali_dotNet Over a year ago

it is almost what I want, thanks for your descriptive comment :)

dvo Over a year ago

@Ali_dotNet I suggest opening a new question for your question here in the comments. I don't know if/how it's possible, but I'm sure there are other ideas out there how you can achieve that. You are trying to sort by alpha if the value is not numeric, but sort by numeric if the value is. Best of luck.

HABO Over a year ago

How does this sort values like '1.12.123 Eins' and '11.21.22 Zwei'?

|

Ilyes · Accepted Answer · 2019-08-05 18:10:51Z

1

Are you just looking for

SELECT *
FROM (
      VALUES
      ('2.5.1 Sonstiges'),
      ('1.1.1 Pflegstandards'),
      ('5.1.7 Arbeitsgemeinschaften'),
      ('1.2.1 Anforderungen'),
      ('2.4.5 Betriebsarzt'),
      ('B Kernprozesse'),
      ('1.1.1.4 Umgang mit'),
      ('2.3.3 Kardiologie')
     ) T(Str)
ORDER BY
CASE WHEN TRY_CAST(LEFT(Str, 1) AS INT) IS NOT NULL
     THEN 0
     ELSE 1
END

Which will returns

+-----------------------------+
|             Str             |
+-----------------------------+
| 1.1.1 Pflegstandards        |
| 5.1.7 Arbeitsgemeinschaften |
| 1.2.1 Anforderungen         |
| 2.4.5 Betriebsarzt          |
| 2.3.3 Kardiologie           |
| 1.1.1.4 Umgang mit          |
| 2.5.1 Sonstiges             |
| B Kernprozesse              |
+-----------------------------+

UPDATE:

According to the last update of your question, you're looking for

WITH A AS
(
  SELECT *, ((LEN(Str) - LEN(REPLACE(Str, '.', ''))) * 2) + 1 N
  FROM (
        VALUES
        ('2.5.1 Sonstiges'),
        ('1.1.1 Pflegstandards'),
        ('5.1.7 Arbeitsgemeinschaften'),
        ('1.2.1 Anforderungen'),
        ('2.4.5 Betriebsarzt'),
        ('B Kernprozesse'),
        ('1.1.1.4 Umgang mit'),
        ('2.3.3 Kardiologie')
       ) T(Str)
)
SELECT *
FROM A
ORDER BY CASE WHEN TRY_CAST(REPLACE(LEFT(Str, N), '.', '') AS INT) IS NULL
              THEN 0
              ELSE CAST(REPLACE(LEFT(Str, N), '.', '') AS INT)
         END

Returns:

+-----------------------------+---+
|             Str             | N |
+-----------------------------+---+
| B Kernprozesse              | 1 |
| 1.1.1 Pflegstandards        | 5 |
| 1.2.1 Anforderungen         | 5 |
| 2.3.3 Kardiologie           | 5 |
| 2.4.5 Betriebsarzt          | 5 |
| 2.5.1 Sonstiges             | 5 |
| 5.1.7 Arbeitsgemeinschaften | 5 |
| 1.1.1.4 Umgang mit          | 7 |
+-----------------------------+---+

Demo

edited Aug 5, 2019 at 18:10

answered Aug 5, 2019 at 17:42

Ilyes

15k5 gold badges31 silver badges61 bronze badges

5 Comments

Ali_dotNet Over a year ago

As stated in updated question, 1.1.1.4 should be the last and 1.2.1 should come right after 1.1.1

Ilyes Over a year ago

@Ali_dotNet How do you determine if there is 3 nulbers? 5? 4? or it's static?

Ali_dotNet Over a year ago

as you see in my query, I use replace in order to remove '.', in fact the number may be in the format 1, 1.1, 1.1.1 or 1.1.1.1

Ali_dotNet Over a year ago

I think this query is removing the leading numbers (if exist at all) and then sorts by the resulting name, I want it to be sorted by numbers (of course after '.' are removed)

Ilyes Over a year ago

@Ali_dotNet Yup, I understand what you want to do and update the answer accordingly.

Collectives™ on Stack Overflow

Numeric sorting SQL server table by column starting with number and string

2 Answers 2

6 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related