How to sort varchar with string and numeric values in sql

Question

I am having a problem to sort varchar with string and numeric values in SQL.

Example:

SELECT Section 
FROM
    (
        SELECT 'SECTION 1--INTRODUCTION' Section UNION ALL
        SELECT 'SECTION 10--ALARMS' Section UNION ALL
        SELECT 'SECTION 11--SYNCHRONIZATION' Section UNION ALL
        SELECT 'SECTION 12--POWER SYSTEMS' Section UNION ALL
        SELECT 'SECTION 13--GROUNDING AND BONDING' Section UNION ALL
        SELECT 'SECTION 2—TELEPHONE EQUIPMENT ORDERS (TEOs)' Section UNION ALL
        SELECT 'SECTION 3--DETAIL ENGINEERING SPECIFICATION REQUIREMENTS' Section UNION ALL
        SELECT 'SECTION 4--CENTRAL OFFICE EQUIPMENT RECORDS' Section UNION ALL
        SELECT 'SECTION 5--EQUIPMENT LAYOUT' Section UNION ALL
        SELECT 'SECTION 6--CARRIER COMMUNICATIONS SPACE EQUIPMENT BUILDING' Section UNION ALL
        SELECT 'SECTION 7--WIRE AND CABLE REQUIREMENTS' Section UNION ALL
        SELECT 'SECTION 8--CABLE RACK, AUXILIARY FRAMING AND LIGHTING SYSTEMS' Section UNION ALL
        SELECT 'SECTION 9 - CROSS CONNECT SYSTEMS' Section UNION ALL
        SELECT '15 MOPS' Section UNION ALL
        SELECT '16 CLECS' Section UNION ALL
        SELECT '2  GENERAL REQUIREMENTS' Section
    ) AS TBL
ORDER BY 
    Section

The above query outputs 15M,16 C,2G,1,10,11,12,13,2 ... but I need my final output to be like below

2  GENERAL REQUIREMENTS
15 MOPS
16 CLECS
SECTION 1--INTRODUCTION
SECTION 2—TELEPHONE EQUIPMENT ORDERS (TEOs)
SECTION 3--DETAIL ENGINEERING SPECIFICATION REQUIREMENTS
SECTION 4--CENTRAL OFFICE EQUIPMENT RECORDS
SECTION 5--EQUIPMENT LAYOUT
SECTION 6--CARRIER COMMUNICATIONS SPACE EQUIPMENT BUILDING
SECTION 7--WIRE AND CABLE REQUIREMENTS
SECTION 8--CABLE RACK, AUXILIARY FRAMING AND LIGHTING SYSTEMS
SECTION 9 - CROSS CONNECT SYSTEMS
SECTION 10--ALARMS
SECTION 11--SYNCHRONIZATION
SECTION 12--POWER SYSTEMS
SECTION 13--GROUNDING AND BONDING

Note that the arrangement and characters put by the users is not consistent.

I already tried one of the answer in this post but nothing works:

SQL Server 2008 - order by strings with number numerically

How do I sort a VARCHAR column in SQL server that contains numbers?

Any help is appreciated.

Thanks!

Matt · Accepted Answer · 2016-12-02 16:10:47Z

1

Edited based on new requirements.

;WITH cte AS (
    SELECT 'SECTION 1--INTRODUCTION' Section UNION ALL
    SELECT 'SECTION 10--ALARMS' Section UNION ALL
    SELECT 'SECTION 11--SYNCHRONIZATION' Section UNION ALL
    SELECT 'SECTION 12--POWER SYSTEMS' Section UNION ALL
    SELECT 'SECTION 13--GROUNDING AND BONDING' Section UNION ALL
    SELECT 'SECTION 2—TELEPHONE EQUIPMENT ORDERS (TEOs)' Section UNION ALL
    SELECT 'SECTION 3--DETAIL ENGINEERING SPECIFICATION REQUIREMENTS' Section UNION ALL
    SELECT 'SECTION 4--CENTRAL OFFICE EQUIPMENT RECORDS' Section UNION ALL
    SELECT 'SECTION 5--EQUIPMENT LAYOUT' Section UNION ALL
    SELECT 'SECTION 6--CARRIER COMMUNICATIONS SPACE EQUIPMENT BUILDING' Section UNION ALL
    SELECT 'SECTION 7--WIRE AND CABLE REQUIREMENTS' Section UNION ALL
    SELECT 'SECTION 8--CABLE RACK, AUXILIARY FRAMING AND LIGHTING SYSTEMS' Section UNION ALL
    SELECT 'SECTION 9 - CROSS CONNECT SYSTEMS' Section UNION ALL
    SELECT '15 MOPS' Section UNION ALL
    SELECT '16 CLECS' Section UNION ALL
    SELECT '2  GENERAL REQUIREMENTS' Section
)

SELECT *
FROM
    cte
ORDER BY
    CAST (
       LEFT(
          RIGHT(SECTION,LEN(Section) - PATINDEX('%[0-9]%',Section) + 1)
             ,PATINDEX('%[^0-9]%'
             ,RIGHT(SECTION,LEN(Section) - PATINDEX('%[0-9]%',Section) + 1) ) - 1
          )
    AS INT)

The idea use PATINDEX to find the first occurrence of a number cut up the string then use PATINDEX again to find the first occurrence of a non numeric character after.

edited Dec 2, 2016 at 16:10

answered Dec 2, 2016 at 15:34

Matt

14.4k2 gold badges19 silver badges29 bronze badges

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

2 Comments

BizApps Over a year ago

Hi @Mat, thanks for your answer , i updated my post and added new values to sort.

Matt Over a year ago

@BizApps I have switched this will look for the 1st number (1,10,etc.) and then order by it. It won't matter if there is no space which hyphen and @ symbol a character etc. it will just extract the first number

Collectives™ on Stack Overflow

How to sort varchar with string and numeric values in sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related