Как отсортировать столбец VARCHAR на сервере SQL, содержащий числа?

Есть несколько способов сделать это.

Один был бы

SELECT
 ...
ORDER BY
  CASE 
    WHEN ISNUMERIC(value) = 1 THEN CONVERT(INT, value) 
    ELSE 9999999 -- or something huge
  END,
  value

первая часть ORDER BY преобразует все в int (с огромным значением для нечисловых чисел, для сортировки последней), затем последняя часть заботится об алфавите.

Обратите внимание, что производительность этого запроса, вероятно, по крайней мере умеренно ужасна для больших объемов данных.

SELECT FIELD FROM TABLE
ORDER BY 
  isnumeric(FIELD) desc, 
  CASE ISNUMERIC(test) 
    WHEN 1 THEN CAST(CAST(test AS MONEY) AS INT)
    ELSE NULL 
  END,
  FIELD

Согласно эта ссылка вам нужно преобразовать в MONEY, а затем INT, чтобы не упорядочивать $ как число.

select
  Field1, Field2...
from
  Table1
order by
  isnumeric(Field1) desc,
  case when isnumeric(Field1) = 1 then cast(Field1 as int) else null end,
  Field1

Это вернет значения в том порядке, в котором вы указали в своем вопросе.

Производительность не будет слишком высокой со всем этим происходящим преобразованием, поэтому другой подход заключается в добавлении еще одного столбца в таблицу, в которой вы храните целочисленную копию данных, а затем сортировку сначала по этому, а затем по рассматриваемому столбцу. Это, очевидно, потребует некоторых изменений в логике, которая вставляет или обновляет данные в таблице, чтобы заполнить оба столбца. Либо так, либо поместите в таблицу триггер для заполнения второго столбца всякий раз, когда данные вставляются или обновляются.

Кажется, это работает:

select your_column  
from your_table  
order by   
case when isnumeric(your_column) = 1 then your_column else 999999999 end,  
your_column   

Одно из возможных решений - дополнить числовые значения символом впереди, чтобы все они имели одинаковую длину строки.

Вот пример использования этого подхода:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

100 следует заменить фактической длиной этого столбца.

SELECT *, CONVERT(int, your_column) AS your_column_int
FROM your_table
ORDER BY your_column_int

ИЛИ ЖЕ

SELECT *, CAST(your_column AS int) AS your_column_int
FROM your_table
ORDER BY your_column_int

Я думаю, оба довольно портативны.

Я решил это очень просто, написав это в части "порядок"

ORDER BY (
sr.codice +0
)
ASC

Кажется, это работает очень хорошо, на самом деле у меня была следующая сортировка:

16079   Customer X 
016082  Customer Y
16413   Customer Z

Так что 0 перед 16082 считается правильным.

вы всегда можете преобразовать свой столбец varchar в bigint, поскольку целое число может быть слишком коротким ...

select cast([yourvarchar] as BIGINT)

но вы всегда должны заботиться об альфа-символах

where ISNUMERIC([yourvarchar] +'e0') = 1

+ 'e0' происходит от http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/isnumeric-isint-isnumber

это привело бы к вашему заявлению

SELECT
  *
FROM
  Table
ORDER BY
   ISNUMERIC([yourvarchar] +'e0') DESC
 , LEN([yourvarchar]) ASC

первый столбец сортировки поместит число вверху. второй сортируется по длине, поэтому 10 будет предшествовать 0001 (что глупо ?!)

это приводит ко второй версии:

SELECT
      *
    FROM
      Table
    ORDER BY
       ISNUMERIC([yourvarchar] +'e0') DESC
     , RIGHT('00000000000000000000'+[yourvarchar], 20) ASC

второй столбец теперь правильно дополняется '0', поэтому естественная сортировка помещает целые числа с ведущими нулями (0,01,10,0100 ...) в правильном порядке (правильно!) - но все альфа-значения будут улучшены с помощью '0' -chars (исполнение)

так третья версия:

 SELECT
          *
        FROM
          Table
        ORDER BY
           ISNUMERIC([yourvarchar] +'e0') DESC
         , CASE WHEN ISNUMERIC([yourvarchar] +'e0') = 1
                THEN RIGHT('00000000000000000000' + [yourvarchar], 20) ASC
                ELSE LTRIM(RTRIM([yourvarchar]))
           END ASC

теперь числа сначала дополняются символами '0' (конечно, длина 20 может быть увеличена) - что позволяет правильно сортировать числа - и только альфа-символы обрезаются

 SELECT *,
       ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical
        FROM
            (

        SELECT '1'Id UNION ALL
        SELECT '25.20' Id UNION ALL

    SELECT 'A115' Id UNION ALL
    SELECT '2541' Id UNION ALL
    SELECT '571.50' Id UNION ALL
    SELECT '67' Id UNION ALL
    SELECT 'B48' Id UNION ALL
    SELECT '500' Id UNION ALL
    SELECT '147.54' Id UNION ALL
    SELECT 'A-100' Id
    )A

    ORDER BY 
    CASE WHEN ISNUMERIC (ID)=0                                /* alphabetical sort */ 
         THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0
                   THEN LEFT(Id,PATINDEX('%[0-9]%',Id))
                   ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1)
              END
    END DESC

Этот запрос полезен для вас. В этом запросе столбец имеет тип данных varchar, упорядоченный в хорошем порядке. Например, в этом столбце данные: - G1, G34, G10, G3. Итак, после выполнения этого запроса вы увидите результаты: - G1, G10, G3, G34.

SELECT *,
       (CASE WHEN ISNUMERIC(column_name) = 1 THEN 0 ELSE 1 END) IsNum
FROM table_name 
ORDER BY IsNum, LEN(column_name), column_name;

Это может вам помочь, я пробовал это, когда у меня возникла такая же проблема.

SELECT * FROM tab ORDER BY IIF(TRY_CAST(val AS INT) IS NULL, 1, 0),TRY_CAST(val AS INT);