Извлечь числа из поля в PostgreSQL

У меня есть таблица со столбцом po_number типа varchar в Postgres 8.4. Он хранит буквенно-цифровые значения с некоторыми специальными символами. Я хочу игнорировать символы [/alpha/?/$/encoding/.] и проверять, содержит ли столбец число или нет. Если это число, то оно должно быть приведено к типу как число или же передать значение null, поскольку мое выходное поле po_number_new является числовым полем.

Ниже приведен пример:

пример

скрипт SQL.

Я устал от этого утверждения:

select 
(case when  regexp_replace(po_number,'[^\w],.-+\?/','') then po_number::numeric
else null
end) as po_number_new from test

Но я получил ошибку для явного приведения:

ошибка


postgresql regex case regexp-replace conditional
person user1538020    schedule 12.11.2016    source источник
comment
Восстановите свой новый вопрос, вы получите ответ. Просто подробно объясните все крайние случаи, вот и все.   -  person Tim Biegeleisen    schedule 14.11.2016
comment
Все в порядке, Тим. С этого момента я буду публиковать правильный вопрос без каких-либо правок :) никаких проблем. Кстати спасибо :)   -  person user1538020    schedule 14.11.2016

Ответы (3)


arrow_upward
52
arrow_downward

Просто:

SELECT NULLIF(regexp_replace(po_number, '\D','','g'), '')::numeric AS result
FROM   tbl;

\D является сокращением класса для "не цифры".
И вам нужен четвертый параметр 'g' (для "глобально"), чтобы заменить все вхождения.
Подробности в руководстве.

Но почему Postgres 8.4? Рассмотрите возможность обновления до современной версии.

Рассмотрим подводные камни устаревших версий:

person Erwin Brandstetter    schedule 12.11.2016

arrow_upward
2
arrow_downward

Я думаю, вы хотите что-то вроде этого:

select (case when regexp_replace(po_number, '[^\w],.-+\?/', '') ~ '^[0-9]+$'
             then regexp_replace(po_number, '[^\w],.-+\?/', '')::numeric
        end) as po_number_new 
from test;

То есть вам нужно сделать преобразование строки после замены.

Примечание. Предполагается, что «число» — это просто строка цифр.

person Gordon Linoff    schedule 12.11.2016
comment
Не могли бы вы объяснить регулярное выражение ,.-+? Что это значит? - person Abelisto; 12.11.2016
comment
@Abelisto Я думаю, что они должны быть в скобках, хотя я сейчас не перед Postgre, чтобы проверить это. - person Tim Biegeleisen; 12.11.2016
comment
Я получаю только нули после использования вышеизложенного... вместо этого мне нужны числа из каждой записи и нули для записей, в которых нет чисел. - person user1538020; 13.11.2016

arrow_upward
1
arrow_downward

Логика, которую я бы использовал, чтобы определить, содержит ли поле po_number числовые цифры, заключается в том, что его длина должна уменьшаться при попытке удалить числовые цифры.

Если это так, то все нечисловые цифры ([^\d]) должны быть удалены из столбца po_number. В противном случае должно быть возвращено NULL.

select case when char_length(regexp_replace(po_number, '\d', '', 'g')) < char_length(po_number)
            then regexp_replace(po_number, '[^0-9]', '', 'g')
            else null
       end as po_number_new
from test
person Tim Biegeleisen    schedule 12.11.2016
comment
Я получаю ошибку SQL [42883] после попытки - person user1538020; 13.11.2016
comment
@ user1538020 Ошибка была вызвана тем, что вы использовали Postgres 8.x, в котором нет функции length. Я обновился, чтобы использовать char_length, и теперь он должен работать. - person Tim Biegeleisen; 13.11.2016
comment
Я выбрал версию(); Сейчас я использую PostgreSQL 9.5.2, скомпилированный Visual C++ build 1800, 64-бит. Все еще получаю ошибку. Я разместил ошибку изображения. - person user1538020; 13.11.2016
comment
@user1538020 user1538020: В стороне: ошибка, которую вы получаете, связана с опечаткой: regex_replace ‹› regexp_replace. И есть есть функция length() на стр. 8.4. - person Erwin Brandstetter; 13.11.2016
comment
@Tim Biegeleisen: выберите случай, когда char_length(regexp_replace(po_number, '\d', '', 'g')) ‹ char_length(po_number), затем regexp_replace(po_number, '[^0-9]', '', 'g ') иначе null заканчивается как po_number_new из теста; Поправил синтаксис, теперь работает. Благодарность.. - person user1538020; 13.11.2016
comment
@ErwinBrandstetter Спасибо, Эрвин, ты действительно король Postgres в Stack Overflow :-) - person Tim Biegeleisen; 13.11.2016