I need an 'ORDER BY' that sorts this format

ABC-1001
ABC-200

so that it sorts like this:

ABC-200
ABC-1001

Here's a script that works:

  SELECT ItemNumber,
  (SELECT SUBSTRING(Items.[ItemNumber],1,CHARINDEX('-',Items.[ItemNumber],1)-1 ))
 as ItemNumberPrefix,
   CONVERT(smallint,(SELECT (Left(SubString(Items.[ItemNumber], PatIndex('%[0-9]%', Items.            [ItemNumber]), 20),
 PatIndex('%[^0-9]%', SubString(Items.[ItemNumber], PatIndex('%[0-9]%', Items.[ItemNumber]), 20) + 'X')-1))))
     as ItemNumberNumeric
  FROM Items
  WHERE ProjectID = 1214
  ORDER BY ItemNumberPrefix, ItemNumberNumeric

How can I make the calculations for 'ItemNumberPrefix' and especially 'ItemNumberNumeric' more efficient?

create table test(ItemNumber varchar(10))
insert into test values('ABC-200'),('ABC-1001')

select * from test
Order by Cast(parsename( replace(ItemNumber,'.','-') ,2)  as int)


drop table test

Jingyang

Your solution is in the right column. Didn't work well.

I guess Jingyang meant to write "CAST(PARSENAME(REPLACE(ItemNumber, '-', '.'), 1) AS int)".

You know how to find the hyphen. Add one and get five characters from that position (only if the number fit as smallint otherwise you could use 10 for int).

create table test(ItemNumber varchar(50))
insert into test values('PA-201 PA-201'),
('PA-201 A PA-202'),
('PA-202 PA-203'),
('PA-203 PA-204'),
('PA-201 PA-206'),
('PA-201 PA-207'),
('PA-201 PA-208'),
('PA-201 PA-209'),
('PA-208 PA-301'),
('PA-201 PA-302'),
('PA-208 PA-303'),
('PA-201 PA-304 B'),
('PA-208 PA-303 A'),
('PA-201 PA-305'),
('PA-201 PA-306'),
('PA-201 PA-307'),
('PA-222 PA-107')








 
select [ItemNumber]
  from test
 Order by
Cast(Reverse(Substring(left(Reverse([ItemNumber]), CHARINDEX('-',Reverse([ItemNumber]))-1)
, CHARINDEX(' ', left(Reverse([ItemNumber]), CHARINDEX('-',Reverse([ItemNumber]))-1)  ) 
,len(left(Reverse([ItemNumber]), CHARINDEX('-',Reverse([ItemNumber]))))))  as int) ASC

drop table test

• Proposed as answer by HunchbackMVP, Moderator Thursday, February 19, 2015 8:14 PM

This?

DECLARE @test TABLE (ItemNumber varchar(50))
INSERT INTO @test  VALUES ('PA-201 PA-201'),('PA-201 A PA-202'),('PA-202 PA-203'),('PA-203 PA-204'),('PA-201 PA-206'),('PA-201 PA-207'),('PA-201 PA-208'),('PA-201 PA-209'),
('PA-208 PA-301'),('PA-201 PA-302'),('PA-208 PA-303'),('PA-201 PA-304 B'),('PA-208 PA-303 A'),('PA-201 PA-305'),('PA-201 PA-306'),('PA-201 PA-307'),('PA-222 PA-107')

SELECT *
  FROM @test
 ORDER BY LEFT(itemNumber, CHARINDEX('-',ItemNumber)-1), LEFT(RIGHT(itemNumber, LEN(itemNumber)-COALESCE(NULLIF(CHARINDEX('-',ItemNumber),0),99)),CHARINDEX(' ',RIGHT(itemNumber, LEN(itemNumber)-CHARINDEX('-',ItemNumber))))

Headache :)

SELECT
	T1.ItemNumber
FROM
	@test AS T1
	CROSS APPLY
	(SELECT REVERSE(T1.ItemNumber) AS col1) AS T2 -- reverse
	CROSS APPLY
	(SELECT PATINDEX('%[0-9]%', T2.col1) AS col1) AS T3 -- last digit
	CROSS APPLY
	(SELECT CHARINDEX('-', T2.col1, T3.col1) AS col1) AS T4 -- last hyphen
ORDER BY
    CAST(REVERSE(SUBSTRING(T2.col1, T3.col1, T4.col1 - T3.col1)) AS smallint);

This reminds me of Brad Schulz.

http://bradsruminations.blogspot.com/

This is what I get, Patrick

PA-201
PA-202
PA-203
PA-204
PA-206
PA-207
PA-208
PA-209
PA-301
PA-302
PA-303
PA-305
PA-306
PA-307
PA-401
PA-402
PA-403
PA-404
PA-702
PA-801
PA-802
PA-803
PA-804
PA-805
PA-806
PA-308
PA-307A
PA-405
PA-309
PA-310
PA-321
PA-311
PA-312
PA-314
PA-315
PA-316
PA-406
PA-407
PA-1001
PA-1002
PA-211
PA-212
PA-213
PA-214
PA-1003
PA-408
PA-409
PA-410
PA-317
PA-318
PA-319
PA-320
PA-1004
PA-1005
PA-215
PA-216
PA-312.1
PA-313
PA-304
PA-322
PA-307.1
PA-323
PA-214.1
PA-210
PA-216.1
PA-1006
PA-1007
PA-701
PA-205
PA-201 A
PA-211 A
PA-214 A
PA-215 A
PA-216 A
PA-303 A
PA-304 B
PA-304 A
PA-306 A
PA-308 A
PA-308 B
PA-308 C
PA-309 A
PA-309 B
PA-309 C
PA-310 A
PA-312 A
PA-312 B
PA-313 A
PA-313 B
PA-313 C
PA-314 A
PA-315 A
PA-316 A
PA-316 B
PA-317 A
PA-317 B
PA-317 C
PA-318 B
PA-318 A
PA-320 A
PA-320 B
PA-321 A
PA-322 A
PA-401 a
PA-402 a
PA-403 a
PA-404 A
PA-405 a
PA-406 a
PA-407 a
PA-408 a
PA-409 a
PA-410 a
PA-701 B
PA-701 A
PT-02
PT-01
SF-01
ST-01
ST-02
WC-01
WC-02
WC-03
WD-01

The suggestion using the APPLY operator is still valid but you will need to convert the string to a numeric value (e.g. numeric(10, 2)) because you have values like "PA-216.1".

It will be helpful if you post most of the patterns in your data. I do not get such error with the last sample data.

DECLARE @test table (ItemNumber varchar(50));

INSERT INTO @test 
VALUES
('PA-201'),
('PA-202'),
('PA-203'),
('PA-204'),
('PA-206'),
('PA-207'),
('PA-208'),
('PA-209'),
('PA-301'),
('PA-302'),
('PA-303'),
('PA-305'),
('PA-306'),
('PA-307'),
('PA-401'),
('PA-402'),
('PA-403'),
('PA-404'),
('PA-702'),
('PA-801'),
('PA-802'),
('PA-803'),
('PA-804'),
('PA-805'),
('PA-806'),
('PA-308'),
('PA-307A'),
('PA-405'),
('PA-309'),
('PA-310'),
('PA-321'),
('PA-311'),
('PA-312'),
('PA-314'),
('PA-315'),
('PA-316'),
('PA-406'),
('PA-407'),
('PA-1001'),
('PA-1002'),
('PA-211'),
('PA-212'),
('PA-213'),
('PA-214'),
('PA-1003'),
('PA-408'),
('PA-409'),
('PA-410'),
('PA-317'),
('PA-318'),
('PA-319'),
('PA-320'),
('PA-1004'),
('PA-1005'),
('PA-215'),
('PA-216'),
('PA-312.1'),
('PA-313'),
('PA-304'),
('PA-322'),
('PA-307.1'),
('PA-323'),
('PA-214.1'),
('PA-210'),
('PA-216.1'),
('PA-1006'),
('PA-1007'),
('PA-701'),
('PA-205'),
('PA-201 A'),
('PA-211 A'),
('PA-214 A'),
('PA-215 A'),
('PA-216 A'),
('PA-303 A'),
('PA-304 B'),
('PA-304 A'),
('PA-306 A'),
('PA-308 A'),
('PA-308 B'),
('PA-308 C'),
('PA-309 A'),
('PA-309 B'),
('PA-309 C'),
('PA-310 A'),
('PA-312 A'),
('PA-312 B'),
('PA-313 A'),
('PA-313 B'),
('PA-313 C'),
('PA-314 A'),
('PA-315 A'),
('PA-316 A'),
('PA-316 B'),
('PA-317 A'),
('PA-317 B'),
('PA-317 C'),
('PA-318 B'),
('PA-318 A'),
('PA-320 A'),
('PA-320 B'),
('PA-321 A'),
('PA-322 A'),
('PA-401 a'),
('PA-402 a'),
('PA-403 a'),
('PA-404 A'),
('PA-405 a'),
('PA-406 a'),
('PA-407 a'),
('PA-408 a'),
('PA-409 a'),
('PA-410 a'),
('PA-701 B'),
('PA-701 A'),
('PT-02'),
('PT-01'),
('SF-01'),
('ST-01'),
('ST-02'),
('WC-01'),
('WC-02'),
('WC-03'),
('WD-01');

SELECT
	T1.ItemNumber
FROM
	@test AS T1
	CROSS APPLY
	(SELECT REVERSE(T1.ItemNumber) AS col1) AS T2 -- reverse
	CROSS APPLY
	(SELECT PATINDEX('%[0-9]%', T2.col1) AS col1) AS T3 -- last digit
	CROSS APPLY
	(SELECT CHARINDEX('-', T2.col1, T3.col1) AS col1) AS T4 -- last hyphen
ORDER BY
    CAST(REVERSE(SUBSTRING(T2.col1, T3.col1, T4.col1 - T3.col1)) AS numeric(10, 2));
GO

Try this one:

DECLARE @test TABLE (ItemNumber varchar(50))
INSERT INTO @test  VALUES 

('PA-201'),('PA-202'),('PA-203'),('PA-204'),('PA-206'),('PA-207'),('PA-208'),('PA-209'),('PA-301'),('PA-302'),('PA-303'),('PA-305'),('PA-306'),('PA-307'),('PA-401'),('PA-402'),('PA-403'),('PA-404'),('PA-702'),('PA-801'),('PA-802'),('PA-803'),
('PA-804'),('PA-805'),('PA-806'),('PA-308'),('PA-307A'),('PA-405'),('PA-309'),('PA-310'),('PA-321'),('PA-311'),('PA-312'),('PA-314'),('PA-315'),('PA-316'),('PA-406'),('PA-407'),('PA-1001'),('PA-1002'),('PA-211'),('PA-212'),('PA-213'),('PA-214'),('PA-1003'),
('PA-408'),('PA-409'),('PA-410'),('PA-317'),('PA-318'),('PA-319'),('PA-320'),('PA-1004'),('PA-1005'),('PA-215'),('PA-216'),('PA-312.1'),('PA-313'),('PA-304'),('PA-322'),('PA-307.1'),('PA-323'),('PA-214.1'),('PA-210'),('PA-216.1'),('PA-1006'),('PA-1007'),('PA-701'),
('PA-205'),('PA-201 A'),('PA-211 A'),('PA-214 A'),('PA-215 A'),('PA-216 A'),('PA-303 A'),('PA-304 B'),('PA-304 A'),('PA-306 A'),('PA-308 A'),('PA-308 B'),('PA-308 C'),('PA-309 A'),('PA-309 B'),('PA-309 C'),('PA-310 A'),('PA-312 A'),('PA-312 B'),('PA-313 A'),('PA-313 B'),
('PA-313 C'),('PA-314 A'),('PA-315 A'),('PA-316 A'),('PA-316 B'),('PA-317 A'),('PA-317 B'),('PA-317 C'),('PA-318 B'),('PA-318 A'),('PA-320 A'),('PA-320 B'),('PA-321 A'),('PA-322 A'),('PA-401 a'),('PA-402 a'),('PA-403 a'),('PA-404 A'),('PA-405 a'),('PA-406 a'),
('PA-407 a'),('PA-408 a'),('PA-409 a'),('PA-410 a'),('PA-701 B'),('PA-701 A'),('PT-02'),('PT-01'),('SF-01'),('ST-01'),('ST-02'),('WC-01'),('WC-02'),('WC-03'),('WD-01')

SELECT *,
LEFT(itemNumber, CHARINDEX('-',ItemNumber)-1), 
 CAST(LEFT(RIGHT(CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)),COALESCE(NULLIF(CHARINDEX(' ',RIGHT( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END))),0),99)) AS FLOAT)
   FROM @test
 ORDER BY LEFT(itemNumber, CHARINDEX('-',ItemNumber)-1), 
 CAST(LEFT(RIGHT(CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)),COALESCE(NULLIF(CHARINDEX(' ',RIGHT( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END))),0),99)) AS FLOAT),
 itemNumber

Nope

PT-01
WC-01
SF-01
WD-01
ST-01
PT-02
WC-02
ST-02
WC-03
PA-201 A
PA-201
PA-202
PA-203
PA-204
PA-205
PA-206
PA-207
PA-208
PA-209
PA-210
PA-211 A
PA-211
PA-212
PA-213
PA-214
PA-214 A
PA-214.1
PA-215 A
PA-215
PA-216
PA-216 A
PA-216.1
PA-301
PA-302
PA-303
PA-303 A
PA-304 B
PA-304 A
PA-304
PA-305
PA-306
PA-306 A
PA-307A
PA-307
PA-307.1
PA-308
PA-308 A
PA-308 B
PA-308 C
PA-309
PA-309 A
PA-309 B
PA-309 C
PA-310 A
PA-310
PA-311
PA-312
PA-312 A
PA-312 B
PA-312.1
PA-313
PA-313 A
PA-313 B
PA-313 C
PA-314
PA-314 A
PA-315
PA-315 A
PA-316
PA-316 A
PA-316 B
PA-317
PA-317 C
PA-317 A
PA-317 B
PA-318 B
PA-318
PA-318 A
PA-319
PA-320
PA-320 B
PA-320 A
PA-321 A
PA-321
PA-322
PA-322 A
PA-323
PA-401 a
PA-401
PA-402
PA-402 a
PA-403 a
PA-403
PA-404
PA-404 A
PA-405
PA-405 a
PA-406
PA-406 a
PA-407
PA-407 a
PA-408
PA-408 a
PA-409
PA-409 a
PA-410
PA-410 a
PA-701 B
PA-701
PA-701 A
PA-702
PA-801
PA-802
PA-803
PA-804
PA-805
PA-806
PA-1001
PA-1002
PA-1003
PA-1004
PA-1005
PA-1006
PA-1007

create table test(ItemNumber varchar(50))
insert into test values('PA-201 PA-201'),
('PA-201 A PA-202'),
('PA-202 PA-203'),
('PA-203 PA-204'),
('PA-201 PA-206'),
('PA-201 PA-207'),
('PA-201 PA-208'),
('PA-201 PA-209'),
('PA-208 PA-301'),
('PA-201 PA-302'),
('PA-208 PA-303'),
('PA-201 PA-304 B'),
('PA-208 PA-303 A'),
('PA-201 PA-305'),
('PA-201 PA-306.1'),
('PA-201 PA-307'),
('PA-222 PA-107A')




 
SELECT [ItemNumber]  FROM test
 Order by
Cast(REVERSE(
Case when CHARINDEX('-',Reverse([ItemNumber]))-1>PatIndex('%[^0-9]%',Reverse([ItemNumber])) 
Then 
Substring( Reverse([ItemNumber]), PatIndex('%[^0-9]%',Reverse([ItemNumber]))+1,CHARINDEX('-',Reverse([ItemNumber]))-PatIndex('%[^0-9]%',Reverse([ItemNumber]))-1)
Else
Substring( Reverse([ItemNumber]),0,CHARINDEX('-',Reverse([ItemNumber])))
ENd) as INT)


drop table test

• Edited by Jingyang LiMicrosoft community contributor, Moderator Thursday, February 19, 2015 9:52 PM

That's not the result I got...

DECLARE @test TABLE (ItemNumber varchar(50))
INSERT INTO @test  VALUES 

('PA-201'),('PA-202'),('PA-203'),('PA-204'),('PA-206'),('PA-207'),('PA-208'),('PA-209'),('PA-301'),('PA-302'),('PA-303'),('PA-305'),('PA-306'),('PA-307'),('PA-401'),('PA-402'),('PA-403'),('PA-404'),('PA-702'),('PA-801'),('PA-802'),('PA-803'),
('PA-804'),('PA-805'),('PA-806'),('PA-308'),('PA-307A'),('PA-405'),('PA-309'),('PA-310'),('PA-321'),('PA-311'),('PA-312'),('PA-314'),('PA-315'),('PA-316'),('PA-406'),('PA-407'),('PA-1001'),('PA-1002'),('PA-211'),('PA-212'),('PA-213'),('PA-214'),('PA-1003'),
('PA-408'),('PA-409'),('PA-410'),('PA-317'),('PA-318'),('PA-319'),('PA-320'),('PA-1004'),('PA-1005'),('PA-215'),('PA-216'),('PA-312.1'),('PA-313'),('PA-304'),('PA-322'),('PA-307.1'),('PA-323'),('PA-214.1'),('PA-210'),('PA-216.1'),('PA-1006'),('PA-1007'),('PA-701'),
('PA-205'),('PA-201 A'),('PA-211 A'),('PA-214 A'),('PA-215 A'),('PA-216 A'),('PA-303 A'),('PA-304 B'),('PA-304 A'),('PA-306 A'),('PA-308 A'),('PA-308 B'),('PA-308 C'),('PA-309 A'),('PA-309 B'),('PA-309 C'),('PA-310 A'),('PA-312 A'),('PA-312 B'),('PA-313 A'),('PA-313 B'),
('PA-313 C'),('PA-314 A'),('PA-315 A'),('PA-316 A'),('PA-316 B'),('PA-317 A'),('PA-317 B'),('PA-317 C'),('PA-318 B'),('PA-318 A'),('PA-320 A'),('PA-320 B'),('PA-321 A'),('PA-322 A'),('PA-401 a'),('PA-402 a'),('PA-403 a'),('PA-404 A'),('PA-405 a'),('PA-406 a'),
('PA-407 a'),('PA-408 a'),('PA-409 a'),('PA-410 a'),('PA-701 B'),('PA-701 A'),('PT-02'),('PT-01'),('SF-01'),('ST-01'),('ST-02'),('WC-01'),('WC-02'),('WC-03'),('WD-01')

SELECT *,
LEFT(itemNumber, CHARINDEX('-',ItemNumber)-1), 
 CAST(LEFT(RIGHT(CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)),COALESCE(NULLIF(CHARINDEX(' ',RIGHT( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END))),0),99)) AS FLOAT)
   FROM @test
 ORDER BY LEFT(itemNumber, CHARINDEX('-',ItemNumber)-1), 
 CAST(LEFT(RIGHT(CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)),COALESCE(NULLIF(CHARINDEX(' ',RIGHT( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END, LEN( CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END)-CHARINDEX('-', CASE WHEN itemNumber LIKE '%[a-z]' THEN LEFT(itemNumber,LEN(itemNumber)-1)+' '+RIGHT(itemNumber,1) ELSE itemNumber END))),0),99)) AS FLOAT),
 itemNumber 

Gave me:

ItemNumber	(No column name)	(No column name)
PA-201		PA				201
PA-201 A		PA				201
PA-202		PA				202
PA-203		PA				203
PA-204		PA				204
PA-205		PA				205
PA-206		PA				206
PA-207		PA				207
PA-208		PA				208
PA-209		PA				209
PA-210		PA				210
PA-211		PA				211
PA-211 A		PA				211
PA-212		PA				212
PA-213		PA				213
PA-214		PA				214
PA-214 A		PA				214
PA-214.1		PA				214.1
PA-215		PA				215
PA-215 A		PA				215
PA-216		PA				216
PA-216 A		PA				216
PA-216.1		PA				216.1
PA-301		PA				301
PA-302		PA				302
PA-303		PA				303
PA-303 A		PA				303
PA-304		PA				304
PA-304 A		PA				304
PA-304 B		PA				304
PA-305		PA				305
PA-306		PA				306
PA-306 A		PA				306
PA-307		PA				307
PA-307A		PA				307
PA-307.1		PA				307.1
PA-308		PA				308
PA-308 A		PA				308
PA-308 B		PA				308
PA-308 C		PA				308
PA-309		PA				309
PA-309 A		PA				309
PA-309 B		PA				309
PA-309 C		PA				309
PA-310		PA				310
PA-310 A		PA				310
PA-311		PA				311
PA-312		PA				312
PA-312 A		PA				312
PA-312 B		PA				312
PA-312.1		PA				312.1
PA-313		PA				313
PA-313 A		PA				313
PA-313 B		PA				313
PA-313 C		PA				313
PA-314		PA				314
PA-314 A		PA				314
PA-315		PA				315
PA-315 A		PA				315
PA-316		PA				316
PA-316 A		PA				316
PA-316 B		PA				316
PA-317		PA				317
PA-317 A		PA				317
PA-317 B		PA				317
PA-317 C		PA				317
PA-318		PA				318
PA-318 A		PA				318
PA-318 B		PA				318
PA-319		PA				319
PA-320		PA				320
PA-320 A		PA				320
PA-320 B		PA				320
PA-321		PA				321
PA-321 A		PA				321
PA-322		PA				322
PA-322 A		PA				322
PA-323		PA				323
PA-401		PA				401
PA-401 a		PA				401
PA-402		PA				402
PA-402 a		PA				402
PA-403		PA				403
PA-403 a		PA				403
PA-404		PA				404
PA-404 A		PA				404
PA-405		PA				405
PA-405 a		PA				405
PA-406		PA				406
PA-406 a		PA				406
PA-407		PA				407
PA-407 a		PA				407
PA-408		PA				408
PA-408 a		PA				408
PA-409		PA				409
PA-409 a		PA				409
PA-410		PA				410
PA-410 a		PA				410
PA-701		PA				701
PA-701 A		PA				701
PA-701 B		PA				701
PA-702		PA				702
PA-801		PA				801
PA-802		PA				802
PA-803		PA				803
PA-804		PA				804
PA-805		PA				805
PA-806		PA				806
PA-1001		PA				1001
PA-1002		PA				1002
PA-1003		PA				1003
PA-1004		PA				1004
PA-1005		PA				1005
PA-1006		PA				1006
PA-1007		PA				1007
PT-01		PT				1
PT-02		PT				2
SF-01		SF				1
ST-01		ST				1
ST-02		ST				2
WC-01		WC				1
WC-02		WC				2
WC-03		WC				3
WD-01		WD				1

The patterns of the data were more complex than my initial example. Sorry for the misdirect.

Here's what worked best:

ORDER BY
		(SELECT SUBSTRING(Items.[ItemNumber] + '-',1,CHARINDEX('-',Items.[ItemNumber] + '-',1)-1 )), --left of hyphen 
		CONVERT(smallint,(SELECT (Left(SubString(Items.[ItemNumber] + '-', PatIndex('%[0-9]%', Items.[ItemNumber] + '-'), 20), 
			PatIndex('%[^0-9]%', SubString(Items.[ItemNumber] + '-', PatIndex('%[0-9]%', Items.[ItemNumber] + '-'), 20) + 'X')-1)))),--numbers right of hyphen
		len(ItemNumber)	-- factors in characters right of numbers 

When you really need to sort your data like this, then it's a violation of 1NF. Cause it means that you store non-atomic values in one column. Fix this and your sorting problem is solved also.