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

Ответ 1

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

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

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

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

Ответ 2

Есть несколько возможных способов сделать это.

Можно было бы

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

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

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

Ответ 3

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

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

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

Ответ 4

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

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

Ответ 5

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

ORDER BY (
sr.codice +0
)
ASC

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

16079   Customer X 
016082  Customer Y
16413   Customer Z

Итак, 0 перед 16082 считается правильно.

Ответ 6

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

select cast([yourvarchar] as BIGINT)

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

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

the + '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'-символы (исполнение)

поэтому третья версия:

 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 может быть увеличена) - который сортирует числа справа - и альфы только обрезаются

Ответ 7

Это работает:

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

Ответ 8

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, чтобы избежать порядка $$ в качестве числа.

Ответ 9

Этот запрос полезен для вас. В этом запросе столбец имеет тип данных 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;

Ответ 10

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

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

Ответ 11

Это проще, чем вышеуказанные способы:

       SELECT *
       FROM table_name
       ORDER BY LENGTH(column_name), column_name

Ответ 12

 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