Обновите значение идентификатора в формате XXXXXXXX-X, используя oracle SQL
Имя таблицы: ТЕСТ Имя столбца: ID [VARCHAR(200)]
Формат идентификатора — ‘XXXXXXXX-X’, где ‘X’ — число от 0 до 9.
Дополнительные операции в случае, если вышеуказанный формат не удовлетворяет:
если идентификатор состоит из 9 цифр и между восьмой и девятой цифрой стоит двойное тире, лишнее тире удаляется (например, 08452142--6 -> 08452142-6)
если идентификатор состоит из 9 цифр и между восьмой и девятой цифрами есть пробелы и/или нецифровые и/или небуквенные символы, замените их на тире (например, 08452142 - . 3 -> 08452142-3)
если идентификатор состоит из 9 цифр и начинается/заканчивается нецифровым и/или небуквенным символом(ами), тогда удалите этот символ(ы) до цифры (например, 08452142-2.. -> 08452142-2)
если идентификатор содержит только 9 цифр, поставьте тире перед последней цифрой (например, 123456789 -> 12345678-9)
Я добился необходимого формата, используя приведенный ниже фрагмент.
UPDATE TEST
SET ID = (SELECT REGEXP_REPLACE(ID,'^\d{8}-\d{1}$','') AS "ID"
from TEST
WHERE PK = 11;
)
Каковы возможные способы добавления преобразований, упомянутых в пунктах [1-4] выше, в одном запросе?
Используя REGEXP_REPLACE, я могу получить идентификатор в указанном выше формате. Но в случае неправильного формата и необходимости преобразования идентификатора [например, удаления лишнего тире или добавления тире в случае получения 9 цифр] для достижения удовлетворительного формата, как этого можно достичь в одном запросе UPDATE?
Ответы 4
Вы можете использовать следующее, я думаю:
UPDATE TEST
SET ID = REGEXP_REPLACE(ID,'^\D*(\d{8})\D*(\d)\D*$','\1-\2')
WHERE REGEXP_LIKE(ID,'^\D*(\d{8})\D*(\d)\D*$')
Таким образом, вы игнорируете все нецифровые символы и ищете 8-значное число, а затем 1-значное число. Возьмите эти 2 числа и поставьте один «-» между ними.
Это немного более щедро, чем вам может понадобиться, но должно работать со всеми предоставленными вами примерами.
Я думаю, вам нужны первые 8 цифр, затем дефис, затем 9-я цифра:
select ( substr(regexp_replace(id, '[^0-9]', ''), 1, 8) ||
'-' ||
substr(regexp_replace(id, '[^0-9]', ''), 9, 1)
)
В любом случае вам нужно извлечь 9 цифр из вашей строки на первом шаге. А потом
добавить дефис перед последним символом. Для обоих шагов используйте функцию regexp_replace()
with test(id) as
(
select '08452142--6' from dual union all
select '08452142 - . 3' from dual union all
select '08452142-2..' from dual union all
select '123456789' from dual union all
select '1234567890' from dual
)
select case when length(regexp_replace(id,'(\D)'))=9 then
regexp_replace(regexp_replace(id,'(\D)'),
'(^[[:digit:]]{8})(.*)([[:digit:]]{1}$)','\1-\3')
end as id
from test;
ID
----------
08452142-6
08452142-3
08452142-2
12345678-9
<null>
Привет @Barbaros, регулярное выражение не работало для строк «..00120792-2..» и «00120792-2..». Я попытался изменить регулярное выражение, добавив (.*) в начале и конце выражения для замены любые другие символы до и после строки «00120792-2», но это не работает.
Привет @gooner_psy. Кажется, до сих пор нет проблем. Пожалуйста, обратите внимание на rextester.com/XBPIG18099.
Привет @BarbarosOzhan, я должен был быть более ясным. В разделе, где я разместил ответ, я использую regexp_replace внутри regexp_like, для которого он не соответствует 2 строкам, упомянутым в моем комментарии. Пожалуйста, проверьте- rextester.com/QSR97396
Я попробовал подход, основанный на предложении @BarbarosÖzhan:
with source as (
select '02426467--6' id from dual union all
select '02426467-6' id from dual union all
select '02597718 -- . 3' id from dual union all
select '02597718 --dF5 . 3' id from dual union all
select '00120792-2..' id from dual union all
select '..00120792-2..' id from dual union all
select '123456789' id from dual union all
select '1234567890' id from dual
)
select
case
when regexp_like(id, '\d{8}-\d{1}')
then id
else
case
when regexp_like(id, '\d{8}-\d{1}')
then id
else
case
when regexp_count(id, '\d') = 9
then
case
when
regexp_like(
regexp_replace(
regexp_replace(
id, '(\d{8}-)(-)(\d{1})', '\1\3'
), '(\d{8})([^A-Za-z1-9])(\d{1})', '\1-\3'
)
, '\d{8}-\d{1}')
then
regexp_replace(
regexp_replace(
id, '(\d{8}-)(-)(\d{1})', '\1\3'
), '(\d{8})([^A-Za-z1-9])(\d{1})', '\1-\3'
)
else id
end
else id
end
end id_tr
from source
Однако в случаях 3 и 4 я не могу избавиться от пробела, точки и букв. Я думаю, что что-то не так с логикой, если длина больше 9. Я заканчиваю "id" как есть, поэтому результат тот же без каких-либо изменений. Любые предложения по улучшению этого?
@saiprasad . . . Есть ли причина, по которой вы не приняли этот ответ?