PostgreSQL을 사용하여 문자열을 정수로 캐스팅하고 캐스트에서 오류가 발생할 경우 0을 가지려면 어떻게 해야 합니까?

PostgreSQL을 사용하여 문자열을 정수로 캐스팅하고 캐스트에서 오류가 발생할 경우 0을 가지려면 어떻게 해야 합니까?

포스트그레에서SQL 저는 바차 열이 있는 테이블이 있습니다.데이터는 정수로 되어 있고 저는 쿼리에서 정수형으로 필요합니다.일부 값은 빈 문자열입니다.다음 항목:

SELECT myfield::integer FROM mytable

확량수ERROR: invalid input syntax for integer: ""

포스트그레스에서 캐스트 중 오류가 발생할 경우 캐스트를 조회하고 0을 가질 수 있는 방법은 무엇입니까?

저도 비슷한 문제로 씨름하고 있었을 뿐, 기능의 오버헤드를 원하지 않았습니다.다음과 같은 질문을 했습니다.

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres는 조건부로 바로 가기 때문에 정수가 아닌 사람이 :: 정수 캐스트를 치지 않아야 합니다.또한 NULL 값도 처리합니다(정규식과 일치하지 않음).

선택하지 않는 대신 0을 사용하려면 CASE 문이 작동해야 합니다.

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;

예외 블록을 사용할 수 있는 자체 변환 함수를 만들 수도 있습니다.

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::INTEGER;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;

테스트:

=# select convert_to_integer('1234');
 convert_to_integer 
--------------------
               1234
(1 row)

=# select convert_to_integer('');
NOTICE:  Invalid integer value: "".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)

=# select convert_to_integer('chicken');
NOTICE:  Invalid integer value: "chicken".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)

저도 같은 종류의 필요성을 느꼈고 이것이 저에게 잘 적용된다는 것을 알게 되었습니다(후기 8.4).

CAST((COALESCE(myfield,'0')) AS INTEGER)

다음을 입증할 몇 가지 테스트 사례:

db=> select CAST((COALESCE(NULL,'0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('','0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('4','0')) AS INTEGER);
 int4
------
    4
(1 row)

db=> select CAST((COALESCE('bad','0')) AS INTEGER);
ERROR:  invalid input syntax for integer: "bad"

필드에 숫자가 아닌 텍스트(예: "100bad")가 있을 경우 regexp_replace를 사용하여 캐스트 앞에서 숫자가 아닌 문자를 제거할 수 있습니다.

CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)

그러면 "b3ad5"와 같은 텍스트/바흐 값도 숫자를 제공합니다.

db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
 regexp_replace
----------------
             35
(1 row)

Chris Cogdon은 솔루션이 "bad"(숫자가 전혀 없음)와 같은 경우를 포함하여 모든 경우에 0을 제공하지 않는다는 우려를 해결하기 위해 다음과 같이 수정된 진술을 했습니다.

CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);

변환할 값이 "bad"와 같은 숫자가 아닌 문자인 경우에만 0을 제공한다는 점을 제외하고는 더 간단한 솔루션과 유사하게 작동합니다.

db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
     coalesce
----------
        0
(1 row)

(0 || myfield)::integer

설명(Postgres 8.4에서 시험):

상기의 표현은 산출됩니다.NULL" NULL " "에 myfield그리고.0빈 문자열의 경우(이 정확한 동작은 사용자의 사용 사례에 적합하거나 적합하지 않을 수 있습니다).

SELECT id, (0 || values)::integer from test_table ORDER BY id

검정 데이터:

CREATE TABLE test_table
(
  id integer NOT NULL,
  description character varying,
  "values" character varying,
  CONSTRAINT id PRIMARY KEY (id)
)

-- Insert Test Data
INSERT INTO test_table VALUES (1, 'null', NULL);
INSERT INTO test_table VALUES (2, 'empty string', '');
INSERT INTO test_table VALUES (3, 'one', '1');

쿼리 결과는 다음과 같습니다.

 ---------------------
 |1|null        |NULL|
 |2|empty string|0   |
 |3|one         |1   |
 ---------------------

선택만 하면 됩니다.values::integer오류 메시지가 표시됩니다.

@매튜의 대답은 좋습니다.하지만 그것은 더 간단하고 빠를 수 있습니다.그리고 빈 문자열을 변환하라는 질문이 나옵니다.''에서 )까지0그러나 다른 "잘못된 입력 구문" 또는 "범위를 벗어남" 입력은 제외됩니다.

CREATE OR REPLACE FUNCTION convert_to_int(text)
  RETURNS int AS
$func$
BEGIN
   IF $1 = '' THEN  -- special case for empty string like requested
      RETURN 0;
   ELSE
      RETURN $1::int;
   END IF;

EXCEPTION WHEN OTHERS THEN
   RETURN NULL;  -- NULL for other invalid input

END
$func$  LANGUAGE plpgsql IMMUTABLE;

은 니다됩반을 반환합니다.0과 빈문과열에 NULL다른 잘못된 입력에 대해.
모든 데이터 유형 변환에 쉽게 적용할 수 있습니다.

예외 블록을 입력하는 것은 상당히 더 비쌉니다.빈 문자열이 일반적인 경우 예외를 제기하기 전에 해당 사례를 검색하는 것이 좋습니다.
빈 문자열이 매우 드문 경우 테스트를 예외 절로 이동하면 비용이 발생합니다.

SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable

저는 포스트그리와 일해본 적이 없습니다.SQL 하지만 SELECT 쿼리에서 IF 문의 구문이 올바른지 설명서를 확인했습니다.

Substring은 경우에 따라 int의 크기를 제한할 수 있습니다.

SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);

CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
  RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;

이 함수는 항상 반환됩니다.0입력 문자열에 숫자가 없는 경우.

SELECT parse_int('test12_3test');

돌아올 것입니다123

마지막으로 잘못된 문자를 무시하고 숫자만 입력하여 텍스트를 숫자로 변환할 수 있습니다.

SELECT (NULLIF(regexp_replace(split_part(column1, '.', 1), '\D','','g'), '') 
    || '.' || COALESCE(NULLIF(regexp_replace(split_part(column1, '.', 2), '\D','','g'),''),'00')) AS result,column1
FROM (VALUES
    ('ggg'),('3,0 kg'),('15 kg.'),('2x3,25'),('96+109'),('1.10'),('132123')
) strings;  

다음 기능은 다음과 같습니다.

• 기본값 사용(error_result) 캐스터블할 수 없는 결과의 경우(예:abc또는999999999999999999999999999999999999999999
• 유지한다null~하듯이null
• 입력에서 공백 및 기타 빈 공간을 잘라냅니다.
• 유효한 것으로 주조된 가치들bigints와 비교됩니다.lower_bound예를 들어 양수 값만 적용

CREATE OR REPLACE FUNCTION cast_to_bigint(text) 
RETURNS BIGINT AS $$
DECLARE big_int_value BIGINT DEFAULT NULL;
DECLARE error_result  BIGINT DEFAULT -1;
DECLARE lower_bound   BIGINT DEFAULT 0;
BEGIN
    BEGIN
        big_int_value := CASE WHEN $1 IS NOT NULL THEN GREATEST(TRIM($1)::BIGINT, lower_bound) END;
    EXCEPTION WHEN OTHERS THEN
        big_int_value := error_result;
    END;
RETURN big_int_value;
END;

데이터가 정수여야 하는데 이러한 값만 정수로 사용하면 된다면, 전체 마일을 이동하여 열을 정수 열로 변환하는 것은 어떨까요?

그런 다음 잘못된 값을 0으로 변환하는 작업을 한 번만 수행할 수 있습니다. 시스템의 데이터가 테이블에 삽입되는 지점에서 말입니다.

위의 변환을 사용하면 해당 테이블에 대한 각 쿼리의 각 단일 행에 대해 Postgres가 해당 값을 계속해서 변환하도록 강제합니다. 이 경우 이 테이블에서 이 열에 대해 많은 쿼리를 수행하면 성능이 심각하게 저하될 수 있습니다.

저는 다음 코드가 쉽고 잘 작동한다는 것을 알았습니다.원래 답변은 여기에 있습니다. https://www.postgresql.org/message-id/371F1510.F86C876B @sferacarta.com

prova=> create table test(t text, i integer);
CREATE

prova=> insert into test values('123',123);
INSERT 64579 1

prova=> select cast(i as text),cast(t as int)from test;
text|int4
----+----
123| 123
(1 row)

도움이 되기를 바랍니다

JPA 2.0 및 Hibernate 5.0.2에서도 작동합니다.

SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword

놀라운 효과.LIKE랑도 통하는 것 같아요.

이 작업도 수행해야 하지만 이 작업은 SQL 전반에 걸쳐 수행되며 사후에는 수행되지 않습니다.

select avg(cast(mynumber as numeric)) from my table

언급URL : https://stackoverflow.com/questions/2082686/how-do-i-cast-a-string-to-integer-and-have-0-in-case-of-error-in-the-cast-with-p