Вопрос:
У меня есть этот запрос
SELECT text FROM book WHERE lyrics IS NULL AND MOD(TO_NUMBER(SUBSTR(text,18,16)),5) = 1
иногда строка выглядит примерно так: $OK$OK$OK$OK$OK$OK$OK, иногда что-то вроде @P,351811040302663;E,101;D,07112018134733,07012018144712;G,4908611,50930248,207,990;M,79379;S,0;IO,3,0,0
если я хотел бы знать, возможно ли предотвратить ORA-01722: недопустимое число, потому что некоторые причины char в этой позиции не являются числом. Я запускаю этот запрос внутри процедуры, обрабатывая все строки в курсоре, если 1 строка не является числом, я не могу обработать любую строку
Лучший ответ:
Вы можете использовать VALIDATE_CONVERSION если Oracle 12c Release 2 (12.2),
WITH book(text) AS (SELECT ‘@P,351811040302663;E,101;D,07112018134733,07012018144712;G,4908611,50930248,207,990;M,79379;S,0;IO,3,0,0’ FROM DUAL UNION ALL SELECT ‘$OK$OK$OK$OK$OK$OK$OK’ FROM DUAL UNION ALL SELECT ’12I45678912B456781234567812345671′ FROM DUAL) SELECT * FROM book WHERE CASE WHEN VALIDATE_CONVERSION(SUBSTR(text,18,16) AS NUMBER) = 1 THEN MOD(TO_NUMBER(SUBSTR(text,18,16)),5) ELSE 0 END = 1 ;
Выход
TEXT 12I45678912B456781234567812345671 Ответ №1 SELECT text FROM book WHERE lyrics IS NULL AND case when regexp_like(SUBSTR(text,18,16),’^[^a-zA-Z]*$’) then MOD(TO_NUMBER(SUBSTR(text,18,16)),5) else null end = 1; Ответ №2
Предполагая, что условие должно быть истинным тогда и только тогда, когда 16-значная подстрока, начинающаяся в позиции 18, состоит из 16 цифр, а число равно 1 по модулю 5, тогда вы можете написать это следующим образом:
… where ….. and case when translate(substr(text, 18, 16), ‘z0123456789’, ‘z’) is null and substr(text, 33, 1) in (‘1’, ‘6’) then 1 end = 1
Это проверит, что подстрока состоит из цифр: функция translate() заменит каждое вхождение z в строке самой, а каждое вхождение 0, 1,…, 9 ни с чем (просто будет удалить их). Необычный вид z необходим из-за нечетной реализации Oracle NULL и пустых строк (вы можете использовать любой другой символ вместо z, но вам нужен символ, поэтому аргумент для translate() равен NULL). Тогда – подстрока состоит из всех цифр тогда и только тогда, когда результат этого перевода равен нулю (пустая строка). И вы по-прежнему проверяете, имеет ли последний символ 1 или 6.
Обратите внимание, что я не использовал регулярные выражения; это важно, если у вас большой объем данных, поскольку стандартные строковые функции, такие как translate(), намного быстрее, чем функции регулярного выражения. Кроме того, все основано на символьном типе данных – нет математических функций, таких как mod(). (То же, что и в ответе Торстен, в котором не было первой части того, что я предложил здесь, – чтобы убедиться, что вся подстрока состоит из цифр.)
