locked
ORDER BY for alphanumeric RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?

    Thursday, February 19, 2015 6:03 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    • Marked as answer by Eric__Zhang Monday, March 2, 2015 2:06 AM
    Friday, February 20, 2015 8:15 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote 
    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

    Thursday, February 19, 2015 6:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Jingyang

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

    PA-201 PA-201

    PA-201 A PA-202

    PA-202 PA-203

    PA-203 PA-204

    PA-204 PA-206

    PA-205 PA-207

    PA-206 PA-208

    PA-207 PA-209

    PA-208 PA-301

    PA-209 PA-302

    PA-210 PA-303

    PA-211 A PA-304 B

    PA-211 PA-303 A

    PA-212 PA-305

    PA-213 PA-306

    PA-214 PA-307

    Thursday, February 19, 2015 6:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

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

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, February 19, 2015 6:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

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

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, February 19, 2015 6:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote 
    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 Thursday, February 19, 2015 8:14 PM
    Thursday, February 19, 2015 7:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

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

    Thursday, February 19, 2015 7:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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/

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, February 19, 2015 7:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Thursday, February 19, 2015 8:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

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

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Edited by HunchbackMVP Thursday, February 19, 2015 8:24 PM
    Thursday, February 19, 2015 8:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hunchback:

    Conversion failed when converting the nvarchar value '307A' to data type int.

    Thursday, February 19, 2015 8:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Thursday, February 19, 2015 8:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Thursday, February 19, 2015 8:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Thursday, February 19, 2015 9:12 PM
  • Question
    Sign in to vote
    0
    Sign in to vote 

    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 Li Thursday, February 19, 2015 9:52 PM
    Thursday, February 19, 2015 9:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    Thursday, February 19, 2015 10:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    • Marked as answer by Eric__Zhang Monday, March 2, 2015 2:06 AM
    Friday, February 20, 2015 8:15 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    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.

    Friday, February 20, 2015 8:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I understand. It's too late, over 100K records in database.

    It's not the worst design error in the database. :-(

    • Edited by mmmtbig Wednesday, February 25, 2015 10:41 PM addition
    Wednesday, February 25, 2015 10:40 PM