ORDER BY specific numerical value in string [SQL]

Question

Have a column ID that I would like to ORDER in a specific format. Column has a varchar data type and always has an alphabetic value, typically P in front followed by three to four numeric values. Possibly even followed by an underscore or another alphabetic value. I have tried few options and none are returning what I desire.

SELECT [ID] FROM MYTABLE
                        ORDER BY
                             (1)   LEN(ID), ID  ASC
                      /      (2)   LEFT(ID,2)
         OPTIONS TRIED       (3)   SUBSTRING(ID,2,4) ASC
                      \      (4)   ROW_NUMBER() OVER (ORDER BY SUBSTRING(ID,2,4))
                             (5)   SUBSTRING(ID,PATINDEX('%[0-9]%',ID),LEN(ID))
                             (6)   LEFT(ID, PATINDEX('%[0-9]%', ID)-1)

Option 1 seems to be closest to what I am looking for except when an _ or Alphabetic values follow the numeric value. See results from Option 1 below

P100
P208
P218
P301
P305
P306
P4200
P4510
P4511
P4512
P5011
P1400A
P4125H
P4202A
P4507L
P4706A
P1001_2
P2103_B
P4368_RL

Would like to see..

P100
P208
P218
P301
P305
P306
P1001_2
P1400A
P2103_B
P4125H
P4200
P4202A
P4368_RL
P4507L
P4510
P4511
P4512
P4706A
P5011

Answer 1

ORDER BY
  CAST(SUBSTRING(id, 2, 4) AS INT),
  SUBSTRING(id, 6, 3)

http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/9464

And one that's still less complex than a getOnlyNumbers() UDF, but copes with varying length of numeric part.

CROSS APPLY
(
  SELECT
    tail_start = PATINDEX('%[0-9][^0-9]%', id + '_')
)
  stats
CROSS APPLY
(
  SELECT
    numeric = CAST(SUBSTRING(id, 2, stats.tail_start-1) AS INT),
    alpha   = RIGHT(id, LEN(id) - stats.tail_start)
)
  id_tuple
ORDER BY
  id_tuple.numeric,
  id_tuple.alpha

http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/9499

Finally, one that can cope with there being no number at all (but still assumes the first character exists and should be ignored).

CROSS APPLY
(
  SELECT
    tail_start = NULLIF(PATINDEX('%[0-9][^0-9]%', id + '_'), 0)
)
  stats
CROSS APPLY
(
  SELECT
    numeric = CAST(SUBSTRING(id, 2, stats.tail_start-1) AS INT),
    alpha   = RIGHT(id, LEN(id) - ISNULL(stats.tail_start, 1))
)
  id_tuple
ORDER BY
  id_tuple.numeric,
  id_tuple.alpha

http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/9507

Answer 2

This is a rather strange way to sort but now that I understand it I figured out a solution. I am using a table valued function here to strip out only the numbers from a string. Since the function returns all numeric characters I also need to check for the _ and only pass in the part of the string before that.

Here is the function.

create function GetOnlyNumbers
(
    @SearchVal varchar(8000)
) returns table as return

    with MyValues as
    (
        select substring(@SearchVal, N, 1) as number
            , t.N
        from cteTally t 
        where N <= len(@SearchVal)
            and substring(@SearchVal, N, 1) like '[0-9]'
    )

    select distinct NumValue = STUFF((select number + ''
                from MyValues mv2
                order by mv2.N
                for xml path('')), 1, 0, '')
    from MyValues mv

This function is using a tally table. If you have one you can tweak that code slightly to fit. Here is my tally table. I keep it as a view.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Next of course we need to have some data to work. In this case I just created a table variable to represent your actual table.

declare @Something table
(
    SomeVal varchar(10)
)

insert @Something values
('P100')
, ('P208')
, ('P218')
, ('P301')
, ('P305')
, ('P306')
, ('P4200')
, ('P4510')
, ('P4511')
, ('P4512')
, ('P5011')
, ('P1400A')
, ('P4125H')
, ('P4202A')
, ('P4507L')
, ('P4706A')
, ('P1001_2')
, ('P2103_B')
, ('P4368_RL')

With all the legwork and setup behind us we can get to the actual query needed to accomplish this.

select s.SomeVal 
from @Something s
cross apply dbo.GetOnlyNumbers(case when charindex('_', s.SomeVal) = 0 then s.SomeVal else left(s.SomeVal, charindex('_', s.SomeVal) - 1) end) x
order by convert(int, x.NumValue)

This returns the rows in the order you listed them in your question.

Answer 3

You can break down ID in steps to extract the number. Then, order by the number and ID. I like to break down long string manipulation into steps using CROSS APPLY. You can do it inline (it'd be long) or bundle it into an inline TVF.

SELECT t.*
FROM MYTABLE t
     CROSS APPLY (SELECT NoP = STUFF(ID, 1, 1, '')) nop
     CROSS APPLY (SELECT FindNonNumeric = LEFT(NoP, ISNULL(NULLIF(PATINDEX('%[^0-9]%', NoP)-1, -1), LEN(NoP)))) fnn
     CROSS APPLY (SELECT Number = CONVERT(INT, FindNonNumeric)) num
ORDER BY Number
       , ID;

Answer 4

I think your best bet is to create a function that strips the numbers out of the string, like this one, and then sort by that. Even better, as @SeanLange suggested, would be to use that function to store the number value in a new column and sort by that.