CODEWITHSUNDEEP
sqlsql-server
BizApps
BizApps

Reputation: 6130

How to sort varchar with string and numeric values in sql

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!

Upvotes: 2

Views: 310

Answers (1)

Matt
Matt

Reputation: 14381

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.

Upvotes: 1

Related Questions