Doctor Doctor - 1 month ago 10
SQL Question

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.

Answer Source

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
...