복붙노트

[SQL] 어떻게 내가 문자열을 정수로 캐스팅과 PostgreSQL과 캐스트에 오류가 발생하는 경우에 0을해야합니까?

SQL

어떻게 내가 문자열을 정수로 캐스팅과 PostgreSQL과 캐스트에 오류가 발생하는 경우에 0을해야합니까?

PostgreSQL의에서 나는 VARCHAR 열이있는 테이블이 있습니다. 데이터는 정수 있어야 내가 쿼리에서 정수 유형에 필요합니다. 일부 값은 빈 문자열입니다. 다음과 같은:

SELECT myfield::integer FROM mytable

수익률 오류 : 정수 잘못된 입력 구문 : ""

어떻게 캐스트를 조회 포스트 그레스에서 주조하는 동안 오류가 발생하는 경우에 0을 가질 수있다?

해결법

  1. ==============================

    1.난 그냥 비슷한 문제로 자신을 씨름했지만, 함수의 오버 헤드를 원하지 않았다. 나는 다음과 같은 쿼리를 함께했다 :

    난 그냥 비슷한 문제로 자신을 씨름했지만, 함수의 오버 헤드를 원하지 않았다. 나는 다음과 같은 쿼리를 함께했다 :

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

    포스트 그레스 바로 가기의 조건문은, 그래서 당신은 아닌 정수가 :: 정수 캐스트를 타격하지합니다. 또한 핸들 (그들은 정규 표현식과 일치하지 않습니다) 값을 NULL.

    당신이 선택하지 않고 0을 원하는 경우, 다음 CASE 문은 작동합니다 :

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

    2.당신은 또한 당신이 예외 블록을 사용할 수있는 내부에 자신의 변환 기능을 만들 수 있습니다 :

    당신은 또한 당신이 예외 블록을 사용할 수있는 내부에 자신의 변환 기능을 만들 수 있습니다 :

    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)
  3. ==============================

    3.내가 필요 같은 종류를했고,이 날 (포스트 그레스 8.4) 잘 작동하는 것으로 확인 :

    내가 필요 같은 종류를했고,이 날 (포스트 그레스 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"와 같은 텍스트 / VARCHAR 값도 숫자를 줄 것이다

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

    이 솔루션은 "나쁜"(전혀 숫자 문자)와 같은 경우를 포함, 모든 경우에 0을 포기하지 않을와 주소 크리스 Cogdon의 관심에,이 조정 된 성명을 발표했다 :

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

    그것은 변환 할 수있는 값은 "나쁜"로만 숫자가 아닌 문자 인 경우 0을 줄 것이다 제외하고, 간단한 솔루션과 유사한 작동합니다 :

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

    4.이것은 다소 해킹 될 수 있지만, 우리의 경우에 일을 가지고 :

    이것은 다소 해킹 될 수 있지만, 우리의 경우에 일을 가지고 :

    (0 || myfield)::integer

    설명 (포스트 그레스 8.4에서 테스트) :

    myfield에서 NULL-값과 빈 문자열에 대한 0의 위에서 언급 한 표현 수익률의 NULL (이 정확한 동작 또는 사용 사례에 맞지 않을 수 없습니다).

    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   |
 ---------------------

    선택 값만 반면 :: 정수는 오류 메시지가 표시됩니다.

    도움이 되었기를 바랍니다.

  5. ==============================

    5.MYTABLE FROM SELECT CASE 때 myfield = ""THEN 0 ELSE myfield :: 정수 END

    MYTABLE FROM SELECT CASE 때 myfield = ""THEN 0 ELSE myfield :: 정수 END

    나는 이제까지 PostgreSQL을 함께 근무 한 적이없는하지만 난의 올바른 구문에 대한 설명서를 확인 SELECT 쿼리 문 IF.

  6. ==============================

    6.@ 매튜의 대답은 좋다. 그러나 그것은 간단하고 빠르게 할 수 있습니다. 그리고 질문은 0으로 빈 문자열 ( '')로 변환하도록 요구,하지만 다른 "잘못된 입력 구문"또는 "범위를 벗어남"입력 :

    @ 매튜의 대답은 좋다. 그러나 그것은 간단하고 빠르게 할 수 있습니다. 그리고 질문은 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;

    이것은 다른 잘못된 입력에 대해 빈 문자열과 NULL 0을 반환합니다. 그것은 쉽게 데이터 형식 변환하도록 구성 될 수있다.

    예외 블록을 입력하면 실질적으로 더 비싸다. 빈 문자열이 일반적인 경우는 예외를 발생하기 전에 케이스를 잡으려고 의미가 있습니다. 빈 문자열은 매우 드문 경우에는 예외 조항에 테스트를 이동 지불한다.

  7. ==============================

    7.

    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)를 반환합니다

  8. ==============================

    8.나는 쉽고 작업에 다음 코드를 발견했다. 원래 대답은 여기 https://www.postgresql.org/message-id/371F1510.F86C876B@sferacarta.com입니다

    나는 쉽고 작업에 다음 코드를 발견했다. 원래 대답은 여기 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)

    그것이 도움이되기를 바랍니다

  9. ==============================

    9.어떤 경우에는 5 월의 도움이 문자열, 당신은 INT의 크기를 제한 할 수 있습니다.

    어떤 경우에는 5 월의 도움이 문자열, 당신은 INT의 크기를 제한 할 수 있습니다.

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

    10.데이터가 정수 있어야하는데, 당신은 정수로 그 가치를 필요로하는 경우, 당신은 왜 전체 마일을 이동 한 정수 컬럼에 열을 변환 할 수 있습니까?

    데이터가 정수 있어야하는데, 당신은 정수로 그 가치를 필요로하는 경우, 당신은 왜 전체 마일을 이동 한 정수 컬럼에 열을 변환 할 수 있습니까?

    그런 다음 데이터가 테이블에 삽입하는 시스템의 시점에서 단 한 번 제로에 불법 값이 변환을 할 수 있습니다.

    위의 변환을 사용하면 해당 테이블에 대한 각 쿼리에서 각 단일 행에 대해 또 다시 그 값을 변환하는 포스트 그레스를 강요 -이 수 심각하게 디그레이 성능이 테이블에서이 열에 대해 쿼리를 많이한다면.

  11. ==============================

    11.다음과 같은 기능을 수행합니다

    다음과 같은 기능을 수행합니다

    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;
  12. ==============================

    12.또한 같은 필요하지만 JPA 2.0 및 최대 절전 모드 5.0.2와 그 작품을 가지고 :

    또한 같은 필요하지만 JPA 2.0 및 최대 절전 모드 5.0.2와 그 작품을 가지고 :

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

    경이로움을 작동합니다. 나는 너무 LIKE와 함께 작동합니다 생각합니다.

  13. ==============================

    13.이것은 또한 작업을해야하지만이 SQL 맞은 편에 특정을 포스트 그레스 없습니다.

    이것은 또한 작업을해야하지만이 SQL 맞은 편에 특정을 포스트 그레스 없습니다.

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

    14. from 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 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 선택 목록에있는 모든 열이 GROUP BY 절에 표시해야합니까  (0) 2020.05.31
[SQL] 기본 키 / 외래 키 이름 지정 규칙 [마감]  (0) 2020.05.31
[SQL] 어떻게 SQL에서 데이터 트리를 대표하는?  (0) 2020.05.31
[SQL] 체크 ADD와 함께 제약 조건 CHECK 제약 대를 추가 CONSTRAINT 다음  (0) 2020.05.31
[SQL] 어떻게 첫 번째 행에 가입하세요  (0) 2020.05.31
,

티스토리툴바