SQL RegEx для подтверждения формата

На чтение 3 мин Опубликовано 07.10.2024

Вопрос или проблема

Я пытаюсь подтвердить значение поля, чтобы оно соответствовало определенному формату.

Вот работа в процессе, которая проходит для большинства значений, но не проходит, когда значение_5 отсутствует (что является обязательным). Также существует ли элегантный способ это подтвердить?

Этот запрос проходит:

SELECT REGEXP_LIKE('3-Abc-Def_Ghi_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5_ValueNumber6','[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}.*');

Этот запрос не проходит:

SELECT REGEXP_LIKE('3-Abc-Def_Ghi_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6','[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}_[A-Za-z0-9]{3,20}.*');

Ниже приведены еще несколько примеров значений для подтверждения решения:

'3-ABC-DEF_GHI_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6'
'23-ABC-DEF_GHI_ValueNumber2_Value3_ValueNumber4'
'23-AB-DEF_GHIJK_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5'

Ответ или решение

Чтобы подтвердить формат строки, содержащей определённые компоненты, вы можете использовать регулярные выражения в SQL. Ваша цель — обеспечить, чтобы строка соответствовала формату, который включает в себя числа, буквы и некоторые разделители, при этом делать часть значений необязательной.

По вашему примеру строка имеет следующий формат:

Первая часть: 1 или 2 цифры.
Вторая часть: 3 буквы (может быть как буквы, так и цифры).
Третья часть: 3 буквы (также может быть как буквы, так и цифры).
Четвёртая часть: 3-17 букв или цифр, следующее за _.
Пятая часть: 3-20 букв или цифр, следующее за _.
Шестая часть: 3-20 букв или цифр, следующее за _.
Седьмая часть: 3-20 букв или цифр, следующее за _ (но это значение может быть отсутствующим).

Чтобы учесть, что пятая часть (или значение_5) может отсутствовать, мы можем использовать оператор ?, который означает "ноль или одно вхождение", для обозначения необязательной части в регулярном выражении.

Вот данное регулярное выражение:

SELECT REGEXP_LIKE(
  '3-Abc-Def_Ghi_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6', 
  '[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_(?:[A-Za-z0-9]{3,20}(_[A-Za-z0-9]{3,20})?)?(?:_[A-Za-z0-9]{3,20})?'
);

Данное регулярное выражение включает в себя:

[0-9]{1,2}: 1 или 2 цифры.
-[A-Za-z0-9]{3}: дефис и 3 буквы или цифры.
_[A-Za-z0-9]{3}: подчеркивание и 3 буквы или цифры.
_[A-Za-z0-9]{3,17}: подчеркивание и 3-17 букв или цифр для четвёртой части.
_(?:[A-Za-z0-9]{3,20}(_[A-Za-z0-9]{3,20})?)?: подчеркивание и 3-20 буквы или цифры, причём эта часть может отсутствовать.
(?:_[A-Za-z0-9]{3,20})?: подчеркивание и 3-20 буквы или цифры — эта часть также может отсутствовать.

Теперь ваше регулярное выражение будет корректно обрабатывать строки как с отсутствующей пятый частью, так и без неё.

Вот примеры проверок с использованием данного метода:

SELECT REGEXP_LIKE('3-ABC-DEF_GHI_ValueNumber2_ValueNumber3_ValueNumber4__ValueNumber6',
                   '[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_(?:[A-Za-z0-9]{3,20}(_[A-Za-z0-9]{3,20})?)?(?:_[A-Za-z0-9]{3,20})?');

SELECT REGEXP_LIKE('23-ABC-DEF_GHI_ValueNumber2_Value3_ValueNumber4',
                   '[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_(?:[A-Za-z0-9]{3,20}(_[A-Za-z0-9]{3,20})?)?(?:_[A-Za-z0-9]{3,20})?');

SELECT REGEXP_LIKE('23-AB-DEF_GHIJK_ValueNumber2_ValueNumber3_ValueNumber4_ValueNumber5',
                   '[0-9]{1,2}-[A-Za-z0-9]{3}-[A-Za-z0-9]{3}_[A-Za-z0-9]{3,17}_[A-Za-z0-9]{3,17}_(?:[A-Za-z0-9]{3,20}(_[A-Za-z0-9]{3,20})?)?(?:_[A-Za-z0-9]{3,20})?');

Таким образом, ваши строки будут корректно проверены на соответствие необходимому формату!