Преобразуйте VARCHAR в INT / NUMBER в предложении WHERE
У меня есть таблица ACTIVITIES_T в БД PostgreSQL. ACTIVITY_CODE - это столбец VARCHAR:
ID (INT) | ACTIVITY_CODE (VARCHAR) | ACTIVITY_TEXT (VARCHAR)
-------------------------------------------------------------
.. .. ..
7 10012 ..
8 10013 ..
9 10014 ..
10 10015 ..
11 SPECIAL ..
У меня есть непрерывный блок псевдо-чисел, 10012..10015, который мне нужно выбрать с помощью оператора BETWEEN a AND b или >= n1 and <= n2.
Но не все записи в ACTIVITY_CODE являются числовыми значениями.
Я пробовал это:
select * from activities_t where activity_code::int >= 10012 and
activity_code::int <= 10015;
но получил ошибку
ERROR: invalid input syntax for integer: SPECIAL
Что, если я действительно хочу исключить все нечисловые? Есть ли ЧИСЛОВЫЙ ДЕЛО, который я могу использовать? Я не хочу использовать подстроку, если есть SPECIAL10012, который я тоже хочу исключить.
Ответы 2
SELECT *
FROM activities_t
WHERE CASE WHEN activity_code ~ '^\d+$' -- only digits
THEN activity_code::int BETWEEN 10012 AND 10015 END;
Оболочка CASE избегает исключения. Учитываются только строки, состоящие только из цифр. (Так что никаких пустых полей тоже!) ELSE не требуется, потому что для CASE по умолчанию используется NULL, и только TRUE подходит для пункта WHERE.
Конечно, здесь нельзя использовать какой-либо простой индекс на activity_code.
Если вам нужен лучший представление, либо очистите дизайн своей таблицы, либо, если это не вариант, создайте индекс выражения:
CREATE INDEX foo_idx ON activities_t((CASE WHEN activity_code ~ '^\d+$'
THEN activity_code::int END));
И сопоставьте выражение в вашем запросе:
SELECT *, CASE WHEN activity_code ~ '^\d+$' THEN activity_code::int END
FROM activities_t
WHERE CASE WHEN activity_code ~ '^\d+$'
THEN activity_code::int END BETWEEN 10012 AND 10015;
Он немного отличается от первого запроса и был бы немного дороже, если бы он не запускал сканирование индекса.
Просто чтобы добавить к тому, что написал @ Эрвин Брандштеттер, при использовании этого в Spring Data будут жалобы на использование оператора :: из вашего @Query.
Чтобы исправить это, выйдите из ::, как показано в этом примере,
https://stackoverflow.com/a/43982706/1005607
\\:\\:
Или, в качестве альтернативы, измените запрос, чтобы использовать функцию cast.
@Query(value = "select * from activities_t where " +
" case when activity_code ~ '^\d+$' " + // only digits
" then cast(activity_code as int) between 10012 and 10015 end ",
nativeQuery=true)
public List<ActivityT> getByActivityCodes();
«СПЕЦИАЛЬНЫЙ» - это текст. Приведение к целому числу вызывает ошибку. Попробуйте использовать
substring(activity_code FROM '\d+')::intвместоactivity_code::int