Сортировка иерархического текста в SQL

У меня есть таблица с полем varchar. В этом поле хранятся иерархические номера, которые я хочу заказать.

Вот пример моих данных:

1
1.1
1.1.1
1.1.2
1.1.3.
1.1.4
1.1.5
1.1.6.
1.1.7
10.
10.1
10.2
10.3
11.
11.1
11.2
2.
2.1
1.2.2
1.2.2.1
1.2.2.2

Как я могу добиться следующего результата с помощью T-SQL:

1
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.1.5
1.1.6
1.1.7
1.2.2
1.2.2.1
1.2.2.2
2.
2.1
10.
10.1
10.2
10.3
11.
11.1
11.2

Я попытался разделить части со следующим SQL, но это не изящно.

SELECT CASE WHEN CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))  = 0
  THEN SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','') 
  ELSE REPLACE(SUBSTRING(SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''),1, CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))),'.','')
  END AS FIRST_PART
 ,CASE WHEN CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))  > 0
      THEN SUBSTRING( 
SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','') 
   ,CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','')) +1
   ,LEN( SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','')  )
   )
  ELSE '0'
  END AS SECOND_PART
FROM TEST_TABLE

Есть ли лучший способ сделать это?

Ответ 1

Попробуйте следующее:

DECLARE @DataSource TABLE
(
    [Value] VARCHAR(12)
);

INSERT INTO @DataSource ([Value])
VALUES ('1')
      ,('1.1')
      ,('1.1.1')
      ,('1.1.2')
      ,('1.1.3.')
      ,('1.1.4')
      ,('1.1.5')
      ,('1.1.6.')
      ,('1.1.7')
      ,('10.')
      ,('10.1')
      ,('10.2')
      ,('10.3')
      ,('11.')
      ,('11.1')
      ,('11.2')
      ,('2.')
      ,('2.1')
      ,('1.2.2')
      ,('1.2.2.1')
      ,('1.2.2.2');

SELECT *
FROM @DataSource
ORDER BY CAST('/' + IIF(RIGHT([Value],1) = '.', LEFT([Value], LEN([Value]) - 1), [Value]) + '/' AS HIERARCHYID);

Более подробную информацию вы можете узнать hierarchyid. Проверка в предложении ORDER BY просто удаляет . в конце значения (если таковые существуют).

Вы можете изменить функцию IIF с помощью простого CASE WHEN следующим образом:

SELECT *
FROM @DataSource
ORDER BY CAST('/' + CASE WHEN  RIGHT([Value],1) = '.' THEN LEFT([Value], LEN([Value]) - 1) ELSE [Value] END + '/' AS HIERARCHYID);

Ответ 2

удалить точки и порядок:

select column from table order by replace(column,'.','')

И нет необходимости конвертировать varchar в номер, если вы хотите, чтобы 10 до 2.