Блог

Главная - Вопросы по программированию - Убедитесь, что varchar следует определенному шаблону

Убедитесь, что varchar следует определенному шаблону

#sql #sql-server #tsql

Вопрос:

 CREATE TABLE CODES(analysis_date Date, code Varchar(50));

INSERT INTO CODES VALUES('2021-01-01','PROD_OP21_115');
INSERT INTO CODES VALUES('2021-02-06','PROD_TR21_009R');
INSERT INTO CODES VALUES('2021-03-14','PROD_TR21_002');
INSERT INTO CODES VALUES('2021-05-01','PROD_OP21_050R');
INSERT INTO CODES VALUES('2020-06-04','PROD_OP20_450');

Например, у меня есть столбец, в котором хранится строка PROD_OP21_182 . Я разрабатываю запрос, который возвращает все правильно введенные коды (удовлетворяющие всем условиям).

  • Это всегда начинается с PROD_ .
  • После PROD_ этого должна быть одна из этих аббревиатур: OP , UG или TR .
  • 21 означает две последние цифры года, указанные в другом столбце (дата анализа).
  • Последняя _ и три цифры.
  • Код может заканчиваться буквой R (это необязательно). Пример: PROD_OP21_182R .
  • Всегда в верхнем регистре.

Это моя попытка:

 select CODE
from CODES
where SUBSTRING(CODE, 1, 4) = 'PROD'
and SUBSTRING(CODE, 5, 1) = '_'
and SUBSTRING(CODE, 6, 2) in ('OP','UG','TR')
and RIGHT(YEAR(analysis_date), 2) = SUBSTRING(CODE, 8, 2) 
and SUBSTRING(CODE, 10, 1) = '_'
and ( 
(len(CODE) = 13)
or 
(len(CODE) = 14) and CODE like '%R')

Это работает частично. Мне все еще нужно проверить, являются ли три символа SUBSTRING(dispatch_number, 11, 3) цифрами и выполняется ли правило прописных букв (просто в коде не может быть строчных букв).

Комментарии:

1. Ты просто пишешь запрос? Вы не спрашиваете нас, как ограничить будущий ввод в таблицу?

2. @АннЛ. Предполагается, что запрос вернет все коды, соответствующие структуре.

3. Я бы, наверное, просто связал кучу LIKE s-не элегантно, но он выполняет свою работу, имеет предсказуемое поведение индексирования и его легко понять. code LIKE CONCAT('PROD_OP', YEAR(analysis_date) % 100, '_[0-9][0-9][0-9]') COLLATE Latin1_General_CS_AS OR code LIKE CONCAT('PROD_OP', YEAR(analysis_date) % 100, '_[0-9][0-9][0-9]R') COLLATE Latin1_General_CS_AS и снова для UG и. TR Повторенное COLLATE можно исключить, если вместо этого вы сделаете его частью определения столбца.

4. @Hallah: ISNUMERIC это плохая функция, которую почти никогда не следует использовать. Например, '-' , '.' и '$' все строки ISNUMERIC считаются «числовыми», что большинство здравомыслящих людей не сделали бы.

5. Если это вообще возможно, вам следует подумать об исправлении этой таблицы. Это нарушает 1NF, храня несколько фрагментов информации в одном кортеже. Это должны быть столбцы в таблице, а затем код может быть вычисляемым столбцом, если поместить всю эту информацию в нужную строку. Большим преимуществом этого будет то, что вы сможете легко создавать ограничения для каждого элемента, чтобы их можно было проверить.

Ответ №1:

Прежде всего, похвала за публикацию образцов данных и структуры таблиц, чтобы любому было легко помочь. Я бы хотел, чтобы все могли это сделать. Как я уже упоминал в своем комментарии, структура таблицы, которую вы здесь имеете, объясняет, почему это так сложно. Вы храните кучу информации в одном кортеже, который нарушает 1NF. Однако мы не всегда можем это исправить и вынуждены запрашивать информацию, которую нам дают.

Это один из способов решить эту проблему. Вместо подстроки я использую parsename. Это немного больше кода, но я нахожу использование подстроки действительно сложным, потому что, когда что-то меняется в логике, вам приходится возиться с каждым экземпляром подстроки, чтобы правильно получить новые значения.

 select *
from CODES
where parsename(replace(code, '_', '.'), 3) COLLATE Latin1_General_CS_AS = 'PROD'
    AND
    (
        left(parsename(replace(code, '_', '.'), 2), 2) in ('OP','UG','TR')
        OR
        try_parse(right(parsename(replace(code, '_', '.'), 2), 2) as int) = datepart(year, analysis_date)
    )
    AND
    (
        parsename(replace(code, '_', '.'), 1) like '[0-9][0-9][0-9]' 
        OR
        parsename(replace(code, '_', '.'), 1) COLLATE Latin1_General_CS_AS like '[0-9][0-9][0-9][R]'
    )

Комментарии:

1. Все должно идти в кортеже, так как каждый код представляет собой буровую скважину в горнодобывающей компании. PROD означает производственную дрель; OP, UG и т. Д. это места расположения жил (открытый карьер, подземный и т.д.). В любом случае, причина, по которой все должно идти в одном кортеже (в дополнение к политике компании), заключается в том, что это экспортируется в программу географического моделирования, и геологи хотят, чтобы каждое имя было как можно более репрезентативным.

Ответ №2:

Этот код, по-видимому, соответствует требованиям. Все сравнения букв [A-Z] выполняются с указанием параметров сортировки Latin1_General_CS_AS, учитывающих регистр.

 select *
from #codes
where substring(code, 1, 5) COLLATE Latin1_General_CS_AS='PROD_'
      and SUBSTRING(code, 6, 2) COLLATE Latin1_General_CS_AS in ('OP','UG','TR')
      and RIGHT(YEAR(analysis_date), 2) = SUBSTRING(CODE, 8, 2)
      and SUBSTRING(code, 10, 4) like '_[0-9][0-9][0-9]'
      and (len(CODE) = 13
           or 
           (len(CODE) = 14 and right(CODE, 1) COLLATE Latin1_General_CS_AS='R'));
 
 analysis_date   code
2021-01-01      PROD_OP21_115
2021-02-06      PROD_TR21_009R
2021-03-14      PROD_TR21_002
2021-05-01      PROD_OP21_050R
2020-06-04      PROD_OP20_450

Ответ №3:

Вы можете использовать параметры сортировки с учетом регистра, например

 ..
and SUBSTRING(CODE, 6, 2) COLLATE Latin1_General_CS_AS in ('OP','UG','TR')
..

и, КАК в случае с простым регулярным выражением, поддерживаемым SQL Server

 .. 
and SUBSTRING(CODE, 11, 3) like '[0-9][0-9][0-9]' 
..

Комментарии:

1. так как есть 3 числа, не должно быть похоже [0-9][0-9][0-9]?

2. @Алла, определенно. Спасибо.

Метки: ,