ORACLE. Функция TRANSLATE

Конечно же, реальность немного сложнее, чем прогон тестовых данных. Что, кстати, только подтверждает следующие факты:

• Изначально идея должна проверяться на модульных тестах - небольших объемах данных, очень простых. Потому что, если не работает простенькая функция, но обнаруживаем мы это спустя 3 часа регрессии - это не гуд.
• После модульных тестов прогоняем интеграционные, потому что, если что-то работает на маленьких тестовых данных, не факт, что будет так же хорошо работать на больших и более-менее реальных объемах.

Итак, настоящая задача - есть у меня табличка с телефонами. Примерно такая

CREATE TABLE phone (
  field_01 NUMBER,
  field_02 DATE,
  field_03 VARCHAR2(50 byte),
  field_04 NUMBER,
  field_05 VARCHAR2(100 byte),
  field_06 VARCHAR2(100 byte),
  field_07 CHAR(100 byte),
  phone_number VARCHAR2(100 byte),
  field_09 VARCHAR2(100 byte)
);

И мне надо 100 телефонов превратить в 200 (реальные данные, конечно, чуть-чуть побольше  ). Дублирующиеся данные - зло, тестировать лучше на уникальных. Что делать? А почему бы и не прибавить к уже существующим телефонам 100 или 1000?

Сказано, сделано. Проверила сначала сам запрос, как мне строку (varchar2) превратить в число, прибавить к нему что-то и потом сконвертировать обратно. Тест прошел успешно.

Но, когда я этот запрос применила к реальной табличке

INSERT INTO phone (
  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  phone_number,
  field_09
)

(SELECT

  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  to_char(to_number(phone_number)+10),
  field_09

from phone);

То словила неприятный эксепшен "Invalid number".

В чем дело? Да в том, что телефоны можно записать по разному:

• 111-11-11
• 8 926 11 22 345
• 8 (926) 111-22-34
• ...

И вот всякие строки с тире в число уже так просто не переведешь...

Что делать? 

Можно воспользоваться функцией TRANSLATE - Функция TRANSLATE анализирует строку str и заменяет в ней все символы, встречающиеся в строке from_mask, на соответствующие символы из to_mask. Для корректной работы функции строки from_mask и to_mask должны иметь одинаковую длину или строка from_mask должна быть длиннее, чем to_mask. Если from_mask длинее, чем to_mask, и в процессе обработки строки str обнаружатся символы, соответствующие одному из символов from_mask, и при этом им не найдется соответствия в to_mask, то такие символы будут удалены из строки str. Если передать from_mask или to_mask, равное NULL, то функция возвратит значение NULL. Сравнение производится с учетом регистра.

Отлично! Накладываем маску "0123456789", все, что ей соответствует, заменим на пробел. 

А потом применим к ней trim - обрезание пробелов в начале и в конце.

И если функция TRANSLATE после обрезания пробелов вернет нам null (то есть в ней больше ничего и не было) - значит, это число! Можно смело добавлять к нему 10, 20 или сколько там нам нужно.

А вот если функция вернет не null, то там не число. И, чтобы не влететь на эксепшен, лучше просто сгенерировать рандомное значение в определенном диапазоне. Так как у меня Мегафон, то и данные я генерю для 926 

Получается, нам надо "или или" - в этом нам поможет оператор CASE. Получаем

case when trim(TRANSLATE(to_char(p01_phone_number_txt),'0123456789', ' ')) is null

  then to_char(to_number(phone_number) + 1111)

  else to_char(trunc(dbms_random.value(89261111111,89268888888), 0))

end

Функция trunc(x, 0) делает число x целочисленным, так как dbms_random генерирует дробные значения.

Иииии, запускаем весь запрос

INSERT INTO phone (
  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  phone_number,
  field_09
)

(SELECT

  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  case when trim(TRANSLATE(to_char(p01_phone_number_txt),'0123456789', ' ')) is null

  then to_char(to_number(phone_number) + 1111)

  else to_char(trunc(dbms_random.value(89261111111,89268888888), 0))

end,

  field_09

from phone);

И снова получаем  "Invalid number" о_О

Смотрим на данные, применив к ним trim(TRANSLATE(to_char(p01_phone_number_txt),'0123456789', ' '))

Видим, что у нас есть некоторые значения формата "892611 22345". С пробелом посередине. И опять, с пробелом - не число, преобразовывать не буду. А, проходя через функцию, получаются ведь все пробелы, что подпадает под первое условие. Функция считает, что мы получили число и радостно отдает его в then, где не менее радостно падает, так как число с пробелом посередине числом не является.

Что делать? Повсюду баги, блин... 

Приходится изгаляться, чтобы их обойти - в функции then прописываем еще один TRANSLATE, который заменит нам все пробелы на нули.

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

substr(x,1,11) - берет строку x и, начиная обрезает все после 11 символа (считать начинает с 1).

Получаем case:

case when trim(TRANSLATE(to_char(p01_phone_number_txt),'0123456789', ' ')) is null

  then to_char(to_number(substr(TRANSLATE(trim(p01_phone_number_txt),' ','0'),1,11)) + 1111)

  else to_char(trunc(dbms_random.value(89261111111,89268888888), 0))

end

А полный запрос:

INSERT INTO phone (
  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  phone_number,
  field_09
)

(SELECT

  field_01,
  field_02,
  field_03,
  field_04,
  field_05,
  field_06,
  field_07,
  case when trim(TRANSLATE(to_char(p01_phone_number_txt),'0123456789', ' ')) is null

  then to_char(to_number(substr(TRANSLATE(trim(p01_phone_number_txt),' ','0'),1,11)) + 1111)

  else to_char(trunc(dbms_random.value(89261111111,89268888888), 0))

end,

  field_09

from phone);