CODEWITHSUNDEEP
sqlsql-serversql-server-2008natural-sort
Doctor
Doctor

Reputation: 57

SQL Alpha Numeric sorting issue

I have found that we could sort a column in table even if it is alpha numeric.

The only problem here I have is if the column data is only characters it throws error.

BEGIN TRAN

USE SomeDatabase
CREATE TABLE dbo.Section
(
       Section varchar(50) NULL
)
INSERT INTO dbo.Section (Section.Section) VALUES ('BB')
INSERT INTO dbo.Section (Section.Section) VALUES ('1 ')
INSERT INTO dbo.Section (Section.Section) VALUES ('AB 1')
INSERT INTO dbo.Section (Section.Section) VALUES ('A21')
INSERT INTO dbo.Section (Section.Section) VALUES ('B2')
INSERT INTO dbo.Section (Section.Section) VALUES ('A11')
INSERT INTO dbo.Section (Section.Section) VALUES ('B20')
INSERT INTO dbo.Section (Section.Section) VALUES ('B21')
INSERT INTO dbo.Section (Section.Section) VALUES ('AB10')
INSERT INTO dbo.Section (Section.Section) VALUES ('A10')
SELECT Section
FROM dbo.Section

SELECT Section
FROM dbo.Section
ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)-1), -- alphabetical sort
         CONVERT(INT,SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort

DROP Table dbo.Section

ROLLBACK

So, sorting 'BB' is the problem. If you remove BB then it all works just fine.

Upvotes: 3

Views: 79

Answers (3)

Oreo
Oreo

Reputation: 555

The obvious problem from looking at your code, is that the PATINDEX will return 0 if it doesn't find anything. Because you then have 0 - 1 as a length, the LEFT function will throw an error.

Try this instead:

...
ORDER BY LEFT(Section
             ,CASE WHEN PATINDEX('%[0-9]%',Section) >= 2
                   THEN PATINDEX('%[0-9]%',Section) - 1
                   ELSE LEN(Section)
                   END
             ), -- alphabetical sort
     CASE WHEN PATINDEX('%[0-9]%',Section) >= 1
          THEN CONVERT(INT,SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section) - PATINDEX('%[0-9]%',Section) - 1))
          END -- numerical sort
...

Upvotes: 1

SqlZim
SqlZim

Reputation: 38033

You can use stuff() instead of substring() like so:

select section
  , alpha = left(section,patindex('%[0-9]%',section+'0')-1)
  , num   = stuff(section,1,patindex('%[0-9]%',section)-1,'')
from section
order by 
    left(section,patindex('%[0-9]%',section+'0')-1)
  , convert(int,stuff(section,1,patindex('%[0-9]%',section)-1,''))

rextester demo: http://rextester.com/JOXUEE9700

output:

+---------+-------+------+
| section | alpha | num  |
+---------+-------+------+
| 1       |       | 1    |
| A10     | A     | 10   |
| A11     | A     | 11   |
| A21     | A     | 21   |
| AB 1    | AB    | 1    |
| AB10    | AB    | 10   |
| B2      | B     | 2    |
| B20     | B     | 20   |
| B21     | B     | 21   |
| BB      | BB    | NULL |
+---------+-------+------+

Upvotes: 1

Maurício Pontalti Neri
Maurício Pontalti Neri

Reputation: 1080

The problem is the Patindex is returning 0 in both order by clause. I corrected it below.

declare  @Section TABLE
(
       Section varchar(50) NULL
)
INSERT INTO @Section (Section) VALUES ('BB')
INSERT INTO @Section (Section) VALUES ('1 ')
INSERT INTO @Section (Section) VALUES ('AB 1')
INSERT INTO @Section (Section) VALUES ('A21')
INSERT INTO @Section (Section) VALUES ('B2')
INSERT INTO @Section (Section) VALUES ('A11')
INSERT INTO @Section (Section) VALUES ('B20')
INSERT INTO @Section (Section) VALUES ('B21')
INSERT INTO @Section (Section) VALUES ('AB10')
INSERT INTO @Section (Section) VALUES ('A10')
SELECT Section
FROM @Section

SELECT Section
FROM @Section

ORDER BY Section
--LEFT(Section,PATINDEX('%[0-9]%',Section)-1), -- alphabetical sort
--         CONVERT(INT,SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort



SELECT Section  
FROM @Section
ORDER BY LEFT(Section,case when PATINDEX('%[0-9]%',Section) < 1 then 1 else PATINDEX('%[0-9]%',Section) end -1), -- alphabetical sort
         case when PATINDEX('%[0-9]%',Section) < 1 then 0 else CONVERT(INT,SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) end -- numerical sort


Section
--------------------------------------------------
BB
1 
AB 1
A21
B2
A11
B20
B21
AB10
A10


Section
--------------------------------------------------
BB
1 
A10
A11
A21
AB 1
AB10
B2
B20
B21

Upvotes: 1

Related Questions