Properly sorting dotted numbers stored as characte

问题:

I have a SQL table that stores a custom item number. Each of these can have a child broken off from it with a separator of .. Each of those can have a child too.

An example of what it could be (again, dynamic, don't know what it will be):

Item Number
1
1.1
1.1.1
1.1.1.1
1.1.1.1.a
1.1.1.1.b
10
11
2.1
2.10
2.2
2.20
20
3
30

The thing that makes this tough is those numbers are created on the fly and not necessarily in order. You may create 5 numbers (1, 2, 3, 4, 5) and then create a child of 1 so it will not be stored in order in the db.

How do I select from the table and order by the Item Number so that it shows properly, as above, when the data isn't stored in that order?

Most solutions I've tried either gives me 1, 2, 3, 4, 5...1.1, 1.2 OR 1, 1.1, 1.1.1, 10, 11...2, 2.1, 20....3, 30, etc.

回答1:

If you have SQL 2008 you can use the new hierarchyid data type:

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30'
)
SELECT *
FROM Items 
ORDER BY Convert(hierarchyid, '/' + ItemNumber + '/');

回答2:

I discussed this in another forum where we came up with an XML solution that was very dynamic. Adam Haines helped to optimize it, which dramatically improved the performance. This version includes a fix to correctly sort alphabetic digits.

Given the following values:

declare @temp table (id varchar(255))

insert into @temp (id) values
  ('1.1.a.1'),('1.1.aa.2'),
  ('1.1.b.3'),('1.1.a.4'),
  ('1.1.a.5'),('1.1.a.6'),
  ('1.1.a.7'),('1.1.a.8'),
  ('1.1.a.9'),('1.1.a.10'),
  ('1.1.a.11'),('1.1.b.1'),
  ('1.1.b.2'),('1.2.a.1'),
  ('1.10.a.1'),('1.11.a.1'),
  ('1.20.a.1'),('101.20.a.2'),
  ('1.20.a.150'),('1.1'),
  ('1.2'),('1')

This query:

declare @xml xml,
        @max_len int

set @xml =
(
select id as id, cast('<i>' + replace(id,'.','</i><i>') + '</i>' as xml)
from @temp
for xml path('id_root'),type
)

select @max_len = max(len(x.i.value('.','varchar(10)')))
from @xml.nodes('/id_root/i') x(i)

select [id]--, srt.srtvalue
from @temp
cross apply(
    select case when ISNUMERIC(x.i.value('.','varchar(10)')) = 1 then right(replicate('0',@max_len) + x.i.value('.','varchar(10)'),@max_len) else x.i.value('.','varchar(10)') end + '.'
    from @xml.nodes('/id_root/i') x(i)
    where x.i.value('../id[1]','varchar(50)') = [@temp].id
    for xml path('')
) as srt(srtvalue)
order by srt.srtvalue

Returns these values:

id
1
1.1
1.1.a.1
1.1.a.4
1.1.a.5
1.1.a.6
1.1.a.7
1.1.a.8
1.1.a.9
1.1.a.10
1.1.a.11
1.1.aa.2
1.1.b.1
1.1.b.2
1.1.b.3
1.2
1.2.a.1
1.10.a.1
1.11.a.1
1.20.a.1
1.20.a.150
101.20.a.2

If you have more than 10 characters in a single digit, you'll have to change the varchar(10) appropriately.

-- James

回答3:

If you want to sort things numerically, don't store them as nvarchar.

The ACTUAL solution is to make these numbers their own int fields for say Version, Versiona, Versionb...

Then ORDER BY Version, Versiona, Versionb

If you are storing numbers as characters, don't expect them to work like numbers.

回答4:

This is meant more as a joke than a real answer. If

• your categories have maximum 4 levels
• you really don't care for preformance

then try this:

WITH Items (ItemNumber) AS (
              SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '-1' UNION ALL SELECT '1.-1' UNION ALL SELECT '1.-1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '1.2000' UNION ALL SELECT '1.-2000' UNION ALL SELECT '2.1'
    UNION ALL SELECT '2.2' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '30.1' UNION ALL SELECT '10.10'
    UNION ALL SELECT '1.-10' UNION ALL SELECT '1.1.1.1'
)

SELECT ItemNumber
FROM 
  ( SELECT
          ItemNumber
        , CASE WHEN ItemNumber LIKE '%.%.%.%' THEN ItemNumber
               WHEN ItemNumber LIKE '%.%.%' THEN ItemNumber + '.0'
               WHEN ItemNumber LIKE '%.%' THEN ItemNumber + '.0.0'
               ELSE ItemNumber + '.0.0.0'
          END AS ItemNumberToParse
    FROM Items
  ) AS tmp
ORDER BY CAST(PARSENAME(ItemNumberToParse, 4) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 3) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 2) AS INT),
         CAST(PARSENAME(ItemNumberToParse, 1) AS INT) ;

Result:

  ItemNumber
    -1
    1.-2000
    1.-10
    1.-1
    1.-1.1
    1
    1.1
    1.1.1
    1.1.1.1
    1.2000
    2
    2.1
    2.2
    3
    10
    10.10
    11
    20
    30
    30.1

回答5:

Some questions:

• How many subcategories can there be?
• Will these always be just numbers or could they ever be letters?
• What is the largest number that could ever be a single value between dots?

If you're using SQL 2008 then I recommend @Vito's answer as that is by far the best way.

If you're using anearlier version then you'll have to do some work.

Here's a SQL 2005 version. I've assumed that the answers to the above questions are 100, always just numbers, and 9999999999 (10 digits).

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30' UNION ALL SELECT '9999999999.9999999999'
), Padded AS (
   SELECT
      ItemNumber,
      Convert(nvarchar(max), '') SortValue,
      ItemNumber Remainder,
      0 Selector
   FROM Items
   UNION ALL
   SELECT
      ItemNumber,
      SortValue + Right('000000000' + Left(Remainder, CharIndex('.', Remainder + '.') - 1), 10),
      Substring(Remainder, CharIndex('.', Remainder + '.') + 1, 2147483647),
      CASE WHEN Remainder LIKE '%.%' THEN 0 ELSE 1 END
   FROM Padded
   WHERE
      Remainder <> ''
)
SELECT ItemNumber
FROM Padded
WHERE Selector = 1
ORDER BY SortValue;

For SQL 2000 it's going to get a little harder...

回答6:

try this query into order by place it's working in my case. I have same version format value into varchar(max) column

order by CAST (Substring( ItemNumberToParse, 1, CharIndex( '.', ItemNumberToParse ) - 1)as int)

回答7:

This works fine for me:

select cast(ItemNumber as nvarchar(25))
from (
    select '3.1' as ItemNumber
    union all
    select '1'
    union all
    select '3.2'
    union all
    select '3.2.1'
    union all
    select '3.2.1.1'
    union all
    select '3.2.1.2'
    union all
    select '2'
    union all
    select '3'
    union all
    select '3.1.1'
) a
order by ItemNumber

Output:

-------------------------
1
2
3
3.1
3.1.1
3.2
3.2.1
3.2.1.1
3.2.1.2

(9 row(s) affected)