Split関数は、2つのパラメータ、分割する文字列、文字列を分割するデリミタを受け取り、Id列とData列を持つテーブルを返す必要があります。また、Id列とData列を持つテーブルを返すSplit関数の呼び出し方も教えてください。Id列はシーケンスを含み、data列は文字列のデータを含みます。 例
SELECT*FROM Split('A,B,C,D',',')
結果は以下のような形式になります。
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
複数のオプションがあります。詳しくはOracleでカンマ区切りの文字列を行に分割するをご覧ください。
SELECTリストにLEVELを列として追加するだけで、返される各行のsequence番号を得ることができます。または、ROWNUMでも十分です。
以下のSQLのいずれかを使用して、それらをFUNCTIONに含めることができます。
INSTRを CONNECT BY 句で使用します。
以下のSQLを使用して、 FUNCTION; SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 データから 6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 7 /
ワード1 ワード2 ワード3 ワード4 ワード5 ワード6
6行選択
SQL>
REGEXP_SUBSTR in CONNECT BY 句。
となります。 SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str 5 データから 6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL 7 /
ワード1 ワード2 ワード3 ワード4 ワード5 ワード6
6行選択
SQL>
CONNECT BY句のREGEXP_COUNTです。
ワード1 ワード2 ワード3 ワード4 ワード5 ワード6
6行選択
SQL>
XMLTABLE**の使用について
SQL> WITH DATA AS 2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual 3 ) 4 SELECT trim(COLUMN_VALUE) str 5 FROM DATA, xmltable(('"' || REPLACE(str, ',', ',"') || '"')) 6 / STR ------------------------------------------------------------------------ ワード1 ワード2 ワード3 ワード4 ワード5 ワード66行選択
SQL>
MODEL**句を使用しています。
使うことができます。 SQL> WITH t AS 2 ( 3 SELECT 'word1, word2, word3, word4, word5, word6' str 4 FROM dual ), 5 モデル_パラメータ AS 6 ( 7 SELECT str AS orig_str , 8 ',' 9 || str 10 || ',' AS mod_str , 11 1 AS start_pos , 12 Length(str) AS end_pos , 13 (Length(str) - Length(Replace(str, ',')))+ 1 AS element_count , 14 0 AS element_no , 15 ROWNUM AS rn 16 FROM t ) 17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str 18 FROM ( 19 SELECT * 20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str) 21 DIMENSION BY (element_no) 22 MEASURES (start_pos, end_pos, element_count) 23 規則の反復(2000) 24 UNTIL (ITERATION_NUMBER+1 = element_count[0]) 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no))+ 1, 26 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) )) 27 WHERE element_no != 0 28 ORDER BY mod_str , 29 element_no 30 /
ワード1 ワード2 ワード3 ワード4 ワード5 ワード6
6行選択
SQL>
Oracleが提供しているDBMS_UTILITYパッケージを利用することもできます。これは様々なユーティリティーサブプログラムを提供します。そのような便利なユーティリティの1つがCOMMA_TO_TABLEプロシージャで、これはカンマで区切られた名前のリストをPL/SQLの名前のテーブルに変換します。
DBMS_UTILITY.COMMA_TO_TABLE]2を読む
Oracle Setup :
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
p_result SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
クエリ。
SELECT ROWNUM AS ID,
COLUMN_VALUE AS Data
FROM TABLE( split_String( 'A,B,C,D' ) );
出力:
ID DATA
-- ----
1 A
2 B
3 C
4 D
機能が必要な場合はこちらをお試しください。
まず、型を作ります。
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
次に、関数を作ります。
CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
IS
l_res_coll T_TABLE_COLL;
l_index number;
BEGIN
l_res_coll := T_TABLE_COLL();
FOR i IN (
WITH TAB AS
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
SELECT id,
SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
FROM
( SELECT ',' || STR || ',' AS STR, id FROM TAB
),
( SELECT level AS lvl FROM dual CONNECT BY level <= 100
)
WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
ORDER BY ID, NAME)
LOOP
IF i.ID = 1001 THEN
l_res_coll.extend;
l_index := l_res_coll.count;
l_res_coll(l_index):= T_TABLE(i.ID, i.name);
END IF;
END LOOP;
RETURN l_res_coll;
END;
/
あとはそこから選択します。
select * from table(TEST_RETURN_TABLE());
を出力します。
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
もちろん、「WITH TAB AS...」の部分は、実際のデータを取得する場所に置き換える必要があります。 クレジット クレジットを参照してください。
この「分割」機能を使用します。
CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
v_res sys_refcursor;
begin
open v_res for
WITH TAB AS
(SELECT p_str STR FROM DUAL)
select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
from
( select ',' || STR || ',' as STR from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1;
return v_res;
end;
この関数は、質問で説明したように、選択したステートメントでは使用できませんが、それでも役立つと思います。
編集:ここにあなたがする必要があるステップがあります。 1。 オブジェクトの作成:タイプempy_typeをobject(value varchar2(512))として作成または置換します。 2。 タイプの作成:empy_typeのテーブルとしてタイプt_empty_typeを作成または置換します。 3。 関数を作成:
関数を作成または置換分割(p_str varchar2)はsms.t_empty_typeを返します。
v_emptype t_empty_type:= t_empty_type();。
v_cnt番号:= 0;。
v_res sys_refcursor; .
v_value nvarchar2(128);。
始める。
v_resを開きます。
TAB AS付き。
(SELECT p_str STR FROM DUAL)。
substr(STR、instr(STR、 '、'、1、lvl)+ 1、instr(STR、 '、'、1、lvl + 1)-instr(STR、 '、'、1、lvl)-1を選択します)名前。
から。
(TABのSTRとして「、」を選択| STR || '、')、
(レベル< = 100でデュアルコネクトからlvlとしてレベルを選択します)。
ここで、lvl< = length(STR)-length(replace(STR、 '、'))-1;。
ループ。
v_resをv_valueにフェッチします。
v_res%NOTFOUND; .
v_emptype.extend; .
v_cnt:= v_cnt + 1;。
v_emptype(v_cnt):= empty_type(v_value);
エンドループ;。
v_resを閉じる;。
v_emptypeを返します。
終わり;。
次に、次のように呼び出します。
SELECT * FROM (TABLE(split('a,b,c,d,g')))
この関数は、入力文字列MYSTRINGのn番目の部分を返します。 2番目の入力パラメータはセパレータです。、SEPARATOR_OF_SUBSTR。 3番目のパラメーターは、必要なN番目のパートです。
注:MYSTRINGはセパレーターで終了する必要があります。
create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
RETURN VARCHAR2
IS
NTH_SUBSTR VARCHAR2(500);
POS1 NUMBER(4);
POS2 NUMBER(4);
BEGIN
IF NTH_PART=1 THEN
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1) INTO POS1 FROM DUAL;
SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
ELSE
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO POS1 FROM DUAL;
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART) INTO POS2 FROM DUAL;
SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
END IF;
RETURN NTH_SUBSTR;
END;
これが一部のボディに役立つことを願っています。このような機能をループで使用して、すべての値を分離できます。
SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
WHILE NO_OF_RECORDS>0
LOOP
PK_RECORD :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
-- do some thing
NO_OF_RECORDS :=NO_OF_RECORDS-1;
END LOOP;
ここでNO_OF_RECORDS、PK_RECORDは一時変数です。
これが役立つことを願っています。
以下のようにしてください。
select
split.field(column_name,1,',','"') name1,
split.field(column_name,2,',','"') name2
from table_name