CODEWITHSUNDEEP
sql-server
Rahul Patel
Rahul Patel

Reputation: 500

Sort String Containing number

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

Upvotes: 1

Views: 105

Answers (2)

Justin
Justin

Reputation: 9724

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 |

Upvotes: 5

AdamL
AdamL

Reputation: 13141

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

Upvotes: 2

Related Questions