ต้องการฟังก์ชัน Split ซึ่งจะใช้พารามิเตอร์สองตัว ได้แก่ สตริงที่จะแยก และตัวคั่นเพื่อแยกสตริงและส่งคืนตารางที่มีคอลัมน์ Id และข้อมูล และวิธีการเรียกใช้ฟังก์ชัน Split ซึ่งจะส่งคืนตารางที่มีคอลัมน์ Id และข้อมูล คอลัมน์ Id จะมีลำดับและคอลัมน์ข้อมูลจะมีข้อมูลของสตริง เช่น.
SELECT*FROM Split('A,B,C,D',',')
ผลลัพธ์ควรอยู่ในรูปแบบด้านล่าง:
|Id | Data
-- ----
|1 | A |
|2 | B |
|3 | C |
|4 | D |
คุณสามารถสร้างตารางดังกล่าวได้อย่างไร:
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;
ด้วยการปรับแต่งเล็กน้อย (เช่น การแทนที่ , ใน [^,] ด้วยตัวแปร) คุณสามารถเขียนฟังก์ชันดังกล่าวเพื่อส่งคืนตารางได้
มีหลายตัวเลือก ดู แยกเดี่ยว สตริงที่คั่นด้วยเครื่องหมายจุลภาคเป็นแถวใน Oracle
คุณเพียงแค่ต้องเพิ่ม LEVEL ในรายการที่เลือกเป็นคอลัมน์ เพื่อรับหมายเลข ลำดับ สำหรับแต่ละแถวที่ส่งคืน หรือ ROWNUM ก็เพียงพอแล้วเช่นกัน
เมื่อใช้ SQL ด้านล่างนี้ คุณสามารถรวมไว้ใน FUNCTION ได้
INSTR ในส่วน เชื่อมต่อโดย:
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 ในส่วน เชื่อมต่อโดย:
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 ในส่วน 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
ใช้ 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>
การใช้ 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>
คุณยังสามารถใช้แพ็คเกจ DBMS_UTILITY ที่ได้รับจาก Oracle ได้ มันมีโปรแกรมย่อยยูทิลิตี้ต่างๆ โปรแกรมอรรถประโยชน์ที่มีประโยชน์อย่างหนึ่งคือ ขั้นตอน COMMA_TO_TABLE ซึ่งจะแปลงรายชื่อที่คั่นด้วยเครื่องหมายจุลภาคให้เป็นตารางชื่อ PL/SQL
อ่าน DBMS_UTILITY.COMMA_TO_TABLE
การตั้งค่าออราเคิล:
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;
คุณไม่สามารถใช้ฟังก์ชันนี้ในคำสั่ง select เช่นเดียวกับที่คุณอธิบายไว้ในคำถาม แต่ฉันหวังว่าคุณจะพบว่ามันยังมีประโยชน์
แก้ไข: นี่คือขั้นตอนที่คุณต้องทำ 1. สร้างวัตถุ: สร้างหรือแทนที่ประเภท empy_type เป็นวัตถุ (ค่า varchar2(512)) 2. สร้างประเภท: สร้างหรือแทนที่ประเภท t_empty_type เป็นตารางของ empy_type 3. สร้างฟังก์ชัน:
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;
จากนั้นเพียงโทรดังนี้:
SELECT * FROM (TABLE(split('a,b,c,d,g')))
ฟังก์ชันนี้ส่งคืนส่วนที่ n ของสตริงอินพุต MYSTRING พารามิเตอร์อินพุตที่สองคือตัวคั่น เช่น SEPARATOR_OF_SUBSTR และพารามิเตอร์ที่สามคือส่วนที่ 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 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
ลองแบบด้านล่างครับ
select
split.field(column_name,1,',','"') name1,
split.field(column_name,2,',','"') name2
from table_name
