Введите строку в целое число - Postgres

Question

Я импортирую данные из таблицы, которая имеет сырые каналы в Varchar, мне нужно импортировать столбец в Varchar в столбец строки. Я пытался использовать <column_name>::integer так же как to_number(<column_name>,'9999999') но я получаю ошибки, так как есть несколько пустых полей, мне нужно извлечь их как пустые или нулевые в новую таблицу.

Пожалуйста, дайте мне знать, если есть функция для того же.

Answer 1

Подсказка: если ваше значение является пустой строкой, вы можете использовать NULLIF, чтобы заменить его на NULL:

SELECT
    NULLIF(your_value, '')::int

Answer 2

Вы можете даже пойти еще дальше и ограничить это объединенное поле, например:-

SELECT CAST(coalesce(<column>, '0') AS integer) as new_field
from <table>
where CAST(coalesce(<column>, '0') AS integer) >= 10; 

Answer 3

Если вам нужно обработать пустые столбцы как NULLs, попробуйте это:

SELECT CAST(nullif(<column>, '') AS integer);

С другой стороны, если у вас есть NULL значения, которых вам нужно избегать, попробуйте:

SELECT CAST(coalesce(<column>, '0') AS integer);

Я согласен, сообщение об ошибке очень поможет.

Answer 4

Единственный способ избежать ошибки из-за NULL, специальных символов или пустой строки - это сделать так:

SELECT REGEXP_REPLACE(COALESCE(<column>, '0'), '[^0-9]*' ,'0')::integer FROM table

Answer 5

Если значение содержит нечисловые символы, вы можете преобразовать значение в целое число следующим образом:

SELECT CASE WHEN <column>~E'^\\d+$' THEN CAST (<column> AS INTEGER) ELSE 0 END FROM table;

Оператор CASE проверяет <столбец>, если он соответствует целочисленному шаблону, он преобразует скорость в целое число, в противном случае он возвращает 0

Answer 6

Я не могу комментировать (слишком мало репутации? Я довольно новый) пост Лукаса.

На моей настройке PG to_number(NULL) не работает, поэтому мое решение будет:

SELECT CASE WHEN column = NULL THEN NULL ELSE column :: Integer END
FROM table

Answer 7

Общая проблема

Наивно введите преобразование любой строки в целое число вроде этого

SELECT ''::integer

Часто приводит к известной ошибке:

Query failed: ERROR: invalid input syntax for integer: ""

Проблема

В PostgreSQL нет предопределенной функции для безопасного преобразования любой строки в целое число.

Решение

Создайте пользовательскую функцию, вдохновленную функцией PHP intval().

CREATE FUNCTION intval(character varying) RETURNS integer AS $$

SELECT
CASE
    WHEN length(btrim(regexp_replace($1, '[^0-9]', '','g')))>0 THEN btrim(regexp_replace($1, '[^0-9]', '','g'))::integer
    ELSE 0
END AS intval;

$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Применение

/* Example 1 */
SELECT intval('9000');
-- output: 9000

/* Example 2 */
SELECT intval('9gag');
-- output: 9

/* Example 3 */
SELECT intval('the quick brown fox jumps over the lazy dog');
-- output: 0

Answer 8

Вы можете использовать этот запрос

SUM(NULLIF(conversion_units, '')::numeric)

Answer 9

Идеальное решение для меня - использовать и

Приведенное выше решение рассматривает следующие крайние случаи.

1. Строка и число: только функция отлично конвертирует в целые числа.

      SELECT NULLIF(REGEXP_REPLACE('string and 12345', '[^0-9]', '', 'g'), '')::bigint;

2. Только строка:regexp_replaceпреобразует нестроковые символы в пустые строки; который не может быть приведен непосредственно к целому числу, поэтому используйтеnullifпреобразовать в ноль

      SELECT NULLIF(REGEXP_REPLACE('only string', '[^0-9]', '', 'g'), '')::bigint;

3. Целочисленный диапазон: Преобразование строки в целое число может привести к ошибке вне диапазона для целочисленного типа . Так что используйтеbigintвместо

      SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

Answer 10

И если в вашем столбце есть десятичные точки

      select NULLIF('105.0', '')::decimal

Answer 11

Мои два цента за приведение любой строки к целому числу:

SELECT REGEXP_REPLACE('0' || "<column>", '[[:alpha:]]', '', 'g')::int

На основе https://www.postgresqltutorial.com/regexp_replace/.

Answer 12

Это работает для меня:

      select (left(regexp_replace(coalesce('<column_name>', '0') || '', '[^0-9]', '', 'g'), 8) || '0')::integer

Для удобства просмотра:

      select (
    left(
        regexp_replace(
            -- if null then '0', and convert to string for regexp
            coalesce('<column_name>', '0') || '',
            '[^0-9]',
            '',
            'g'
        ),      -- remove everything except numbers
        8       -- ensure ::integer doesn't overload
    ) || '0'    -- ensure not empty string gets to ::integer
)::integer