SQL Order Varchar by 1, 2, 2A, 3, 3A

[bmacbmac]

Hi. I have a varchar column in SQL 2000. It contains numeric and alphanumeric characters: 1, 2, 2A, 3, 3A, 4, 5, 6, 7, 8, 9, 10, 10A, 11, 12, 12A, etc

ORDER BY MyColumn puts the items in order of 1, 10, etc.

I'd like to be in order by:
1
2
2A
3
3A
4
5
6
7
8
9
10
10A
11
11A

I tried padding with leading zeros, but the A throws it off. I end up with:
0001
0002
002A
0003
003A

Which also is not the right order.

Anyone have an idea how I can make this sort order work?

Thanks!

 

[Rhys666]

How about a function to strip non alpha's from a varchar, and a function to strip non numerics from a varchar, and use the result of these for ordering;

Strip Non Numeric Characters function...

CREATE FUNCTION dbo.RemoveNonNumericCharacters
(
	@Temp VARCHAR(1000)
)
RETURNS INT
AS
BEGIN
    WHILE (PATINDEX('%[^0-9]%', @Temp) > 0)
	BEGIN
        SET @Temp = STUFF(@Temp, PATINDEX('%[^0-9]%', @Temp), 1, '');
	END

    RETURN CAST(@Temp AS INT);
END
GO

Strip Non Alpha Characters function...

CREATE FUNCTION dbo.RemoveNonAlphaCharacters
(
	@Temp VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE (PATINDEX('%[^a-z]%', @Temp) > 0)
	BEGIN
        SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z]%', @Temp), 1, '');
	END

    RETURN @Temp;
END
GO

To Test...

CREATE TABLE #TestMyOrdering
(
	ID	VARCHAR(5)	NOT NULL
);

INSERT INTO #TestMyOrdering SELECT '7';
INSERT INTO #TestMyOrdering SELECT '6';
INSERT INTO #TestMyOrdering SELECT '1';
INSERT INTO #TestMyOrdering SELECT '11';
INSERT INTO #TestMyOrdering SELECT '8';
INSERT INTO #TestMyOrdering SELECT '10';
INSERT INTO #TestMyOrdering SELECT '9';
INSERT INTO #TestMyOrdering SELECT '11A';
INSERT INTO #TestMyOrdering SELECT '2';
INSERT INTO #TestMyOrdering SELECT '1A';
INSERT INTO #TestMyOrdering SELECT '4';
INSERT INTO #TestMyOrdering SELECT '5';
INSERT INTO #TestMyOrdering SELECT '3';
INSERT INTO #TestMyOrdering SELECT '7A';

SELECT 
	ID
	,dbo.RemoveNonNumericCharacters(ID)	AS 'Numerics'
	,dbo.RemoveNonAlphaCharacters(ID)	AS 'Alphas'
FROM 
	#TestMyOrdering
ORDER BY 
	dbo.RemoveNonNumericCharacters(ID) 
	,dbo.RemoveNonAlphaCharacters(ID);

DROP TABLE #TestMyOrdering;

Results...

ID	Numerics	Alphas
1	1	
1A	1		A
2	2	
3	3	
4	4	
5	5	
6	6	
7	7	
7A	7		A
8	8	
9	9	
10	10	
11	11	
11A	11		A

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett