[PostgreSQL] STRING_TO_ARRAY 를 Oracle, Goldilocks 로 Conversion 하기

STRING_TO_ARRAY( str1, str2 )

PostgreSQL 에서만 지원하는 함수이며 str1 를 str2 문자열로 구분하는 함수입니다.

또한 구분된 문자열을 배열변수에 담을 수 있습니다.

이기종 데이터베이스에서는 지원하지 않는 함수여서 위 함수 역할을 하는 프로시져를 만들어 보았습니다.

PostgreSQL

STRING_TO_ARRAY 함수의 사용법 입니다.

postgres=# SELECT STRING_TO_ARRAY('ABC;DEF;GHI', ';');
 string_to_array
-----------------
 {ABC,DEF,GHI}
(1 row)

위의 ABC, DEF, GHI 를 PostgreSQL Procedure(혹은 Function) 배열변수에 담는 방법입니다.

CREATE OR REPLACE FUNCTION STRING_TO_ARRAY_TEST ()
RETURNS integer
AS
$BODY$
DECLARE
  ta Text;
  tc Text[];
BEGIN
  ta = 'ABC;DEF;GHI';
  tc = STRING_TO_ARRAY(ta, ';');
  RAISE Notice 'STRING_TO_ARRAY tc[1]=%', tc[1];
  RAISE Notice 'STRING_TO_ARRAY tc[2]=%', tc[2];
  RAISE Notice 'STRING_TO_ARRAY tc[3]=%', tc[3];
  
  RETURN 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE 
;

CREATE FUNCTION

postgres=# SELECT STRING_TO_ARRAY_TEST();
NOTICE:  STRING_TO_ARRAY tc[1]=ABC
NOTICE:  STRING_TO_ARRAY tc[2]=DEF
NOTICE:  STRING_TO_ARRAY tc[3]=GHI

To Goldilocks Database

STRING_TO_ARRAY 함수 역할 및 잘린 문자열을 배열변수에 담는 프로시져 입니다.

CREATE OR REPLACE FUNCTION STRING_TO_ARRAY_TEST
RETURN integer 
IS
 i  Integer;
 ret Integer;
 ta Varchar(4000);

 TYPE rec1 IS TABLE OF Varchar(4000) INDEX BY Integer;
 tc rec1;
BEGIN
  i := 1;
  ta := 'ABC;DEF;GHI';
  
  LOOP
	 ret := INSTR( ta, ';' );
	 tc(i) := SUBSTR(ta, 1, INSTR('ABC;DEF;GHI', ';') - 1);
	 ta := SUBSTR(ta, INSTR(ta, ';') + 1);
	 DBMS_OUTPUT.PUT_LINE('NOTICE:   STRING_TO_ARRAY tc[' || i || ']=' || tc(i));
	 i := i + 1;
	 
	 EXIT WHEN ret = 0;
  END LOOP;  
  
  RETURN 1;
END;
/

호출하면 다음과 같은 결과를 얻게됩니다.

gSQL> CALL STRING_TO_ARRAY_TEST() INTO :V1;
NOTICE:   STRING_TO_ARRAY tc[1]=ABC
NOTICE:   STRING_TO_ARRAY tc[2]=DEF
NOTICE:   STRING_TO_ARRAY tc[3]=GHI

To Oracle Database

STRING_TO_ARRAY 함수 역할 및 잘린 문자열을 배열변수에 담는 프로시져 입니다.

CREATE OR REPLACE FUNCTION STRING_TO_ARRAY_TEST
RETURN integer 
IS
 i  Integer;
 ret Integer;
 ta Varchar(4000);

 TYPE rec1 IS TABLE OF Varchar(4000) INDEX BY PLS_INTEGER;
 tc rec1;
BEGIN
  i := 1;
  ta := 'ABC;DEF;GHI';
  
  LOOP
	 ret := INSTR( ta, ';' );
	 tc(i) := SUBSTR(ta, 1, INSTR('ABC;DEF;GHI', ';') - 1);
	 ta := SUBSTR(ta, INSTR(ta, ';') + 1);
	 DBMS_OUTPUT.PUT_LINE('NOTICE:   STRING_TO_ARRAY tc[' || i || ']=' || tc(i));
	 i := i + 1;
	 
	 EXIT WHEN ret = 0;
  END LOOP;  
  
  RETURN 1;
END;
/

호출하면 다음과 같은 결과를 얻게됩니다.

SQL> CALL STRING_TO_ARRAY_TEST() INTO :V1;
NOTICE:   STRING_TO_ARRAY tc[1]=ABC
NOTICE:   STRING_TO_ARRAY tc[2]=DEF
NOTICE:   STRING_TO_ARRAY tc[3]=GHI