Oracle 10g SQL Сортировка VARCHAR2

У меня проблема с сортировкой с оракулом 10g. Не уверен, является ли это определенным для 10 г или нет.

У меня есть следующая таблица:

ID  NAME
 1  A.1
 2  A.3
 3  A.4
 4  A.5
 5  A.2
 6  A.5.1
 7  A.5.2
 8  A.5.10
 9  A.5.10.1
10  A.5.3

Выполнение дженерика SELECT NAME FROM table_name ORDER BY 1 производит:

A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.10
A.5.10.1
A.5.2
A.5.3

Я бы хотел, чтобы он сортировался правильно, когда эти разделы имеют номера больше 9, например:

A.1
A.2
A.3
A.4
A.5
A.5.1
A.5.2
A.5.3
A.5.10
A.5.10.1

У меня есть намного больше записей чисел, чем это, с различной длиной и многими разделами с сегментами чисел больше 10. Я пытался возиться с regexp_replace() в предложении order by, но безуспешно. Любая помощь будет принята с благодарностью.

Источник

5 ответов

Решение

Попробуй это

WITH t AS
(
  SELECT id,name,
  xmltype('<r><c>' ||replace(NAME, '.', '</c><c>')||'</c></r>') AS xmlname
  FROM table1
)

SELECT name ,id
FROM t
ORDER BY lpad(extract(xmlname,'//c[1]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[2]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[3]/text()').getstringval(), 5, '0')
||lpad(extract(xmlname,'//c[4]/text()').getstringval(), 5, '0')

Вот скрипка

Источник

Следующее может дать вам представление о том, что делать. Чтобы упорядочить значения формы "А.", вы можете упорядочить по длине выражения, за которым следует выражение. Таким образом, A.1 и A.2 до A.10, потому что их длина короче.

Вы можете расширить это с помощью заказа следующим образом:

order by substr(val, 1, instr('.')),
         len(substr(val, 1, instr('.', 1, 2)),
         substr(val, 1, instr('.', 1, 2)),
         len(substr(val, 1, instr('.', 1, 3)),
         substr(val, 1, instr('.', 1, 3)) . . .
Источник

Вот способ сделать это. Я не говорю, что это единственный или даже лучший способ, но это путь:

SELECT ID,
       NAME
FROM
  (SELECT ID, NAME,
       INSTR(NAME, '.', 1, 1) AS FIRST_DOT_INDEX,
       INSTR(NAME, '.', 1, 2) AS SECOND_DOT_INDEX,
       INSTR(NAME, '.', 1, 3) AS THIRD_DOT_INDEX,
       INSTR(NAME, '.', 1, 4) AS FOURTH_DOT_INDEX
     FROM test_table)
ORDER BY SUBSTR(NAME, 1, FIRST_DOT_INDEX-1),
         TO_NUMBER(SUBSTR(NAME, FIRST_DOT_INDEX+1, (CASE WHEN SECOND_DOT_INDEX>0
                                                      THEN SECOND_DOT_INDEX-1
                                                      ELSE LENGTH(NAME)
                                                    END - FIRST_DOT_INDEX))),
         TO_NUMBER(CASE WHEN SECOND_DOT_INDEX = 0 AND THIRD_DOT_INDEX = 0
                     THEN '0'
                     ELSE SUBSTR(NAME, SECOND_DOT_INDEX+1, (CASE WHEN THIRD_DOT_INDEX>0
                                                              THEN THIRD_DOT_INDEX-1
                                                              ELSE LENGTH(NAME)
                                                            END - SECOND_DOT_INDEX)) 
                   END),
         TO_NUMBER(CASE WHEN THIRD_DOT_INDEX > 0
                     THEN SUBSTR(NAME, THIRD_DOT_INDEX+1, LENGTH(NAME) - THIRD_DOT_INDEX)
                     ELSE '0'
                   END);

Поделитесь и наслаждайтесь.

Источник

Использование регулярных выражений может решить вашу проблему,

select *
from new_table
  order by to_number(regexp_replace(name,'[[:alpha:].]*'));

Что означает этот запрос, что я заменяю буквенные символы + символ '.' символ из столбца ИМЯ, прикрывая номер и затем сортируя.

Я надеюсь, что это было полезно, наслаждайтесь!

Источник

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

Oracle SQL doesn't support lookaround assertions, which would be useful for this case:

s/([0-9](?<![0-9]))/0\1/g

You'll have to use at least two replacements:

REGEXP_REPLACE(REGEXP_REPLACE(col, '([0-9]+)', '0\1'), '0([0-9]{2})', '\1')`

Спасибо acheong87 за решение. Соответствие Oracle SQL Regexp_replace

Источник
Другие вопросы по тегам