I think that the OP needs to clarify what the "rules" are......or is this just homework (first post from OP) ?
anyways the two solutions provided give the requested results based on the sample data given by OP
However if the sample data is extended to include other possibilities,then the answers given by David and Luis are different.
CREATE TABLE #temp
(
Section varchar(50) NULL
)
INSERT INTO #temp (Section) VALUES ('1')
INSERT INTO #temp (Section) VALUES ('2')
INSERT INTO #temp (Section) VALUES ('11')
INSERT INTO #temp (Section) VALUES ('AB')
INSERT INTO #temp (Section) VALUES ('BC')
INSERT INTO #temp (Section) VALUES ('CD')
INSERT INTO #temp (Section) VALUES ('0115AB')
INSERT INTO #temp (Section) VALUES ('001abc')
INSERT INTO #temp (Section) VALUES ('001xyz')
INSERT INTO #temp (Section) VALUES ('123xyz')
INSERT INTO #temp (Section) VALUES ('123abc')
INSERT INTO #temp (Section) VALUES ('987abc')
INSERT INTO #temp (Section) VALUES ('100000')
INSERT INTO #temp (Section) VALUES ('AB0001')
SELECT Section
FROM #temp
ORDER BY
SIGN(PATINDEX('%[a-zA-Z]%',Section)) ASC,
CAST('0'+LEFT(Section,PATINDEX('%[a-zA-Z]%',Section+'A')-1) as int) DESC,
RIGHT(Section,LEN(Section)-(PATINDEX('%[a-zA-Z]%',Section+'A')-1)) ASC
SELECT *
FROM #temp
ORDER BY
CASE
WHEN Section NOT LIKE '%[^0-9]%' THEN CAST( Section AS int) * -1
WHEN Section LIKE '%[0-9]%' THEN 2
ELSE 3
END;
DROP TABLE #temp;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day