Perlu fungsi Split yang akan mengambil dua parameter, string untuk dipisah dan pembatas untuk memisahkan string dan mengembalikan tabel dengan kolom Id dan Data. Dan cara memanggil fungsi Split yang akan mengembalikan tabel dengan kolom Id dan Data. Kolom Id akan berisi urutan dan kolom data akan berisi data string. Misalnya.
SELECT*FROM Split('A,B,C,D',',')
Hasilnya Harus dalam format di bawah ini:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
Inilah cara Anda membuat tabel seperti itu:
SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;
Dengan sedikit penyesuaian (yaitu, mengganti , di [^,] dengan variabel) Anda dapat menulis fungsi seperti itu untuk mengembalikan tabel.
Ada beberapa pilihan. Lihat Pisahkan single string yang dipisahkan koma ke dalam baris di Oracle
Anda hanya perlu menambahkan LEVEL pada daftar pilihan sebagai kolom, untuk mendapatkan nomor urutan pada setiap baris. Atau, ROWNUM juga sudah cukup.
Dengan menggunakan salah satu SQL di bawah ini, Anda dapat memasukkannya ke dalam FUNCTION.
INSTR dalam klausa 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 FROM DATA 6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0 7 / STR ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
REGEXP_SUBSTR dalam klausa 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 FROM DATA 6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL 7 / STR ---------------------------------------- word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
REGEXP_COUNT dalam klausa 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 FROM DATA 6 CONNECT BY LEVEL
Menggunakan 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
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6
6 rows selected.
SQL>
Menggunakan klausa MODEL:
SQL> WITH t AS 2 ( 3 SELECT 'word1, word2, word3, word4, word5, word6' str 4 FROM dual ) , 5 model_param 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 RULES ITERATE (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 / STR ------------------------------------------ word1 word2 word3 word4 word5 word6 6 rows selected. SQL>
Anda juga dapat menggunakan paket DBMS_UTILITY yang disediakan oleh Oracle. Ini menyediakan berbagai subprogram utilitas. Salah satu utilitas yang berguna adalah prosedur COMMA_TO_TABLE, yang mengubah daftar nama yang dipisahkan koma menjadi tabel nama PL/SQL.
Baca DBMS_UTILITY.COMMA_TO_TABLE
Pengaturan Oracle:
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;
/
Kueri
SELECT ROWNUM AS ID,
COLUMN_VALUE AS Data
FROM TABLE( split_String( 'A,B,C,D' ) );
Keluaran:
ID DATA
-- ----
1 A
2 B
3 C
4 D
Jika Anda memerlukan suatu fungsi, coba ini.
Pertama kita akan membuat tipe:
CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
Field1 int
, Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/
Kemudian kita akan membuat fungsinya:
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;
/
Sekarang kita dapat memilihnya:
select * from table(TEST_RETURN_TABLE());
Keluaran:
SQL> select * from table(TEST_RETURN_TABLE());
FIELD1 FIELD2
---------- -------------------------
1001 A
1001 B
1001 C
1001 D
1001 E
1001 F
6 rows selected.
Jelas Anda harus mengganti bit WITH TAB AS... dengan tempat Anda mendapatkan data sebenarnya. Kredit Kredit
Gunakan fungsi 'Pisahkan' ini:
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;
Anda tidak dapat menggunakan fungsi ini dalam pernyataan pilih seperti yang Anda jelaskan dalam pertanyaan, tapi saya harap fungsi ini masih berguna bagi Anda.
EDIT: Berikut adalah langkah-langkah yang perlu Anda lakukan. 1. Buat Objek: buat atau ganti tipe empy_type sebagai objek (nilai varchar2(512)) 2. Buat Tipe: buat atau ganti tipe t_empty_type sebagai tabel empy_type 3. Buat Fungsi:
CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
v_emptype t_empty_type := t_empty_type();
v_cnt number := 0;
v_res sys_refcursor;
v_value nvarchar2(128);
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;
loop
fetch v_res into v_value;
exit when v_res%NOTFOUND;
v_emptype.extend;
v_cnt := v_cnt + 1;
v_emptype(v_cnt) := empty_type(v_value);
end loop;
close v_res;
return v_emptype;
end;
Lalu panggil saja seperti ini:
SELECT * FROM (TABLE(split('a,b,c,d,g')))
Fungsi ini mengembalikan bagian ke-n dari string input MYSTRING. Parameter masukan kedua adalah pemisah yaitu SEPARATOR_OF_SUBSTR dan parameter ketiga adalah Bagian ke-N yang diperlukan.
Catatan: MYSTRING harus diakhiri dengan pemisah.
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;
Semoga ini bisa membantu beberapa orang, Anda dapat menggunakan fungsi ini seperti ini dalam satu lingkaran untuk memisahkan semua nilai:
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;
Di sini NO_OF_RECORDS,PK_RECORD adalah variabel sementara.
Semoga ini membantu.
Kueri Terbaik Untuk dipisahkan koma dalam Kueri Ini kami Mengonversi Baris Menjadi Kolom ...
SELECT listagg(BL_PRODUCT_DESC, ', ') within
group( order by BL_PRODUCT_DESC) PROD
FROM GET_PRODUCT
-- WHERE BL_PRODUCT_DESC LIKE ('%WASH%')
WHERE Get_Product_Type_Id = 6000000000007
Coba seperti di bawah ini
select
split.field(column_name,1,',','"') name1,
split.field(column_name,2,',','"') name2
from table_name
