Sort String Containing number

Question

Current OutPut:

PM_DIG_OUTPUT_1_CLOSED  
PM_DIG_OUTPUT_10_CLOSED  
PM_DIG_OUTPUT_14_CLOSED  
PM_DIG_OUTPUT_15_CLOSED  
PM_DIG_OUTPUT_16_CLOSED  
PM_DIG_OUTPUT_2_CLOSED  
PM_DIG_OUTPUT_3_CLOSED

Expected Output:

PM_DIG_OUTPUT_1_CLOSED  
PM_DIG_OUTPUT_2_CLOSED  
PM_DIG_OUTPUT_3_CLOSED  
PM_DIG_OUTPUT_10_CLOSED  
PM_DIG_OUTPUT_14_CLOSED  
PM_DIG_OUTPUT_15_CLOSED  
PM_DIG_OUTPUT_16_CLOSED

Index of Number is not fixed
What is the best way to achieve this order?

EDIT: Some records also contain following data
PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO1 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO2 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO3 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO4

Do any of the methods in http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005 help you? — til_b, Oct 24 '13 at 11:40

Justin · Answer 1 · 2013-10-24T12:26:30.323

Query:

SQLFIDDLEExample

SELECT *
FROM Table1 t1
ORDER BY CAST(REPLACE(REPLACE(col, 'PM_DIG_OUTPUT_', ''),'_CLOSED', '') AS int)

Result:

|                     COL |
|-------------------------|
|  PM_DIG_OUTPUT_1_CLOSED |
|  PM_DIG_OUTPUT_2_CLOSED |
|  PM_DIG_OUTPUT_3_CLOSED |
| PM_DIG_OUTPUT_10_CLOSED |
| PM_DIG_OUTPUT_14_CLOSED |
| PM_DIG_OUTPUT_15_CLOSED |
| PM_DIG_OUTPUT_16_CLOSED |

EDITED ANSWER

You could use query: SQLFIDDLEExample

SELECT *
FROM Table1 t1
ORDER BY CASE WHEN LEFT(col, 2) = 'PM' 
              THEN CAST(REPLACE(REPLACE(col, 'PM_DIG_OUTPUT_', ''),'_CLOSED', '') AS int)
              ELSE RIGHT(col,1) END

Result:

|                                           COL |
|-----------------------------------------------|
|                        PM_DIG_OUTPUT_1_CLOSED |
| PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO1 |
| PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO2 |
|                        PM_DIG_OUTPUT_2_CLOSED |
|                        PM_DIG_OUTPUT_3_CLOSED |
| PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO3 |
| PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO4 |
|                       PM_DIG_OUTPUT_10_CLOSED |
|                       PM_DIG_OUTPUT_14_CLOSED |
|                       PM_DIG_OUTPUT_15_CLOSED |
|                       PM_DIG_OUTPUT_16_CLOSED |

Thanks Justin for your Answer but I can't use this as Its not the case when result will always contain "PM_DIG_OUTPUT_" and "_CLOSED" — Rahul Patel, Oct 24 '13 at 12:04
Thank you Justin, I got the expected output from frikozoid's answer — Rahul Patel, Oct 24 '13 at 15:47

AdamL · Accepted Answer · 2013-10-24T12:18:40.563

2

Assuming that after the number there's only one underscore and some text (then no matter what is before the number, if it's at least one underscore and some text).

Edit for new values. It will sort by the numbers if it can find them and won't break if no numbers were found by matching the pattern:

select 
    *,
    substring(y,len(y)-charindex('_',reverse(y))+2,100) as num
from (
    select
        *,
        substring(x,1,len(x)-charindex('_',reverse(x))) as y
    from (
        select 'PM_DIG_OUTPUT_1_CLOSED' as x union all
        select 'PM_DIG_OUTPUT_10_CLOSED' union all
        select 'PM_DIG_OUTPUT_14_CLOSED' union all
        select 'PM_DIG_OUTPUT_15_CLOSED' union all
        select 'PM_DIG_OUTPUT_16_CLOSED' union all
        select 'PM_DIG_OUTPUT_2_CLOSED' union all
        select 'PM_DIG_OUTPUT_3_CLOSED' union all 
        select 'PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO1' union all
        select 'PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO2' union all
        select 'PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO3' union all
        select 'PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO4'        
    ) x
) y
order by 
    case when isnumeric(substring(y,len(y)-charindex('_',reverse(y))+2,100))=1 then cast(substring(y,len(y)-charindex('_',reverse(y))+2,100) as int) end

edited Oct 24 '13 at 12:18

answered Oct 24 '13 at 11:44

AdamL

12,421
5
50
74

Thank you very much for the quick reply, Its working fine for sample data which I provided in question but failing for some records like PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO1 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO2 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO3 PRM_CODE_MIO_DIGITALOUT_WRITE_LOGIC_CARD2_DO4 – Rahul Patel Oct 24 '13 at 12:07
@RahulPatel You didn't exactly say what's the rule for extracting the number. Even in example from your comment, there are two numbers. By which one the two results should be sorted? – AdamL Oct 24 '13 at 12:10
Thanks for the update.Yes, This is what I was looking for. Also, After Analysis of more records from my Database I found few more patterns.So, I realized that I need to change some records itself to fit for a sorting pattern. – Rahul Patel Oct 24 '13 at 15:44
@RahulPatel Or you could go with CLR functions and RegEx. Would be much better than this mess :). – AdamL Oct 24 '13 at 16:28

Sort String Containing number

2 Answers2