Helpex - Trao đổi & giúp đỡ Đăng nhập

Hàm tách trong oracle thành các giá trị được phân tách bằng dấu phẩy với trình tự tự động

Hoàng Quang Phúc
· 15:20 23/02/2015
22 giờ trước

Cần hàm Split sẽ lấy hai tham số là chuỗi để tách và dấu phân tách để tách chuỗi và trả về một bảng có cột Id và Dữ liệu Và cách gọi hàm Chia sẽ trả về một bảng có cột Id và Dữ liệu. Cột Id sẽ chứa chuỗi và cột dữ liệu sẽ chứa dữ liệu của chuỗi. Ví dụ.

SELECT*FROM Split('A,B,C,D',',')

Kết quả phải ở định dạng dưới đây:

|Id | Data
 --   ----
|1  | A  |
|2  | B  |
|3  | C  |
|4  | D  |
30 hữu ích 1 bình luận 188k xem chia sẻ
Dương Chí Uy
· 15:30 23/02/2015
15:30:30 23/02/2015

Đây là cách bạn có thể tạo một bảng như vậy:

 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;

Với một chút tinh chỉnh (ví dụ, thay thế ,trong [^,]với một biến), bạn có thể viết một hàm như vậy để trả về một bảng.

23 hữu ích 0 bình luận chia sẻ
Ngô Xuân Ninh
· 15:49 23/02/2015
15:49:03 23/02/2015

Có nhiều lựa chọn. Xem Chia chuỗi được phân cách bằng dấu phẩy đơn thành các hàng trong Oracle

Bạn chỉ cần thêm LEVEL trong danh sách chọn dưới dạng một cột, để lấy số thứ tự cho mỗi hàng được trả về. Hoặc, ROWNUM cũng sẽ đủ.

Sử dụng bất kỳ SQL nào dưới đây, bạn có thể đưa chúng vào một FUNCTION .

INSTR trong mệnh đề CONNECT BY :

SQL> VỚI DỮ LIỆU NHƯ
  2 (CHỌN 'word1, word2, word3, word4, word5, word6' str TỪ kép
  3)
  4 SELECT trim (regexp_substr (str, '[^,] +', 1, LEVEL)) str
  5 TỪ DỮ LIỆU
  6 KẾT NỐI BẰNG hướng dẫn (str, ',', 1, LEVEL - 1)> 0
  7 /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

Đã chọn 6 hàng.

SQL

REGEXP_SUBSTR trong mệnh đề CONNECT BY :

SQL> VỚI DỮ LIỆU NHƯ
  2 (CHỌN 'word1, word2, word3, word4, word5, word6' str TỪ kép
  3)
  4 SELECT trim (regexp_substr (str, '[^,] +', 1, LEVEL)) str
  5 TỪ DỮ LIỆU
  6 KẾT NỐI BẰNG regexp_substr (str, '[^,] +', 1, LEVEL) KHÔNG ĐẦY ĐỦ
  7 /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

Đã chọn 6 hàng.

SQL

REGEXP_COUNT trong mệnh đề CONNECT BY :

SQL> VỚI DỮ LIỆU NHƯ
  2 (CHỌN 'word1, word2, word3, word4, word5, word6' str TỪ kép
  3)
  4 SELECT trim (regexp_substr (str, '[^,] +', 1, LEVEL)) str
  5 TỪ DỮ LIỆU
  6 KẾT NỐI THEO CẤP ĐỘ 

Sử dụng XMLTABLE

SQL> VỚI DỮ LIỆU NHƯ
  2 (CHỌN 'word1, word2, word3, word4, word5, word6' str TỪ kép
  3)
  4 SELECT trim (COLUMN_VALUE) str
  5 TỪ DỮ LIỆU, xmltable (('"' || REPLACE (str, ',', '", "') || '"'))
  6 /
STR
-------------------------------------------------- ----------------------
word1
word2
word3
word4
word5
word6

Đã chọn 6 hàng.

SQL

Sử dụng mệnh đề MODEL :

SQL với t AS
  2 (
  3 CHỌN 'word1, word2, word3, word4, word5, word6' str
  4 TỪ kép),
  5 model_param AS
  6 (
  7 CHỌN str AS orig_str,
  số 8 ','
  9 || str
 10 || ',' AS mod_str,
 11 1 AS start_pos,
 12 Chiều dài (str) AS end_pos,
 13 (Chiều dài (str) - Chiều dài (Thay thế (str, ','))) + 1 AS phần tử_count,
 14 0 AS element_no,
 15 ROWNUM NHƯ rn
 16 TỪ t)
 17 SELECT trim (Substr (mod_str, start_pos, end_pos-start_pos)) str
 18 TỪ (
 19 CHỌN *
 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 QUY TẮC NÓI (2000)
 24 NỮA (ITERATION_NUMBER + 1 = element_count [0])
 25 (start_pos [ITERATION_NUMBER + 1] = hướng dẫn (cv (mod_str), ',', 1, cv (element_no)) + 1,
 26 end_pos [iteration_number + 1] = Guid (cv (mod_str), ',', 1, cv (element_no) + 1)))
 27 WHERE element_no! = 0
 28 ĐẶT HÀNG BẰNG mod_str,
 29 element_no
 30 /

STR
------------------------------------------
word1
word2
word3
word4
word5
word6

Đã chọn 6 hàng.

SQL

Bạn cũng có thể sử dụng gói DBMS_UTILITY do Oracle cung cấp. Nó cung cấp các chương trình con tiện ích khác nhau. Một tiện ích hữu ích như vậy là thủ tục COMMA_TO_TABLE , chuyển đổi danh sách tên được phân tách bằng dấu phẩy thành bảng tên PL / SQL.

Đọc DBMS_UTILITY.COMMA_TO_TABLE

22 hữu ích 0 bình luận chia sẻ
Đặng Khánh Minh
· 12:36 24/02/2016
12:36:42 24/02/2016

Thiết lập 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;
/

Truy vấn

SELECT ROWNUM AS ID,
       COLUMN_VALUE AS Data
FROM   TABLE( split_String( 'A,B,C,D' ) );

Đầu ra :

ID DATA
-- ----
 1 A
 2 B
 3 C
 4 D
7 hữu ích 0 bình luận chia sẻ
Nguyễn Nhân Sâm
· 15:47 23/02/2015
15:47:51 23/02/2015

Nếu bạn cần một chức năng, hãy thử điều này.
Đầu tiên, chúng tôi sẽ tạo một loại:

CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
    Field1 int
    , Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/

Sau đó, chúng tôi sẽ tạo hàm:

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

Bây giờ chúng ta có thể chọn từ nó:

select * from table(TEST_RETURN_TABLE()); 

Đầu ra:

SQL> select * from table(TEST_RETURN_TABLE());

    FIELD1 FIELD2
---------- -------------------------
      1001 A
      1001 B
      1001 C
      1001 D
      1001 E
      1001 F

6 rows selected.

Rõ ràng là bạn cần phải thay thế WITH TAB AS...bit bằng nơi bạn sẽ lấy dữ liệu thực tế của mình từ đó. Tín dụng Tín dụng

4 hữu ích 1 bình luận chia sẻ
Hồ Thành Gia
· 10:20 03/10/2016
10:20:52 03/10/2016

Sử dụng chức năng 'Tách' này:

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;

Bạn không thể sử dụng chức năng này trong câu lệnh select như bạn đã mô tả trong câu hỏi, nhưng tôi hy vọng bạn sẽ thấy nó vẫn hữu ích.

CHỈNH SỬA: Đây là các bước bạn cần làm. 1. Create Object: tạo hoặc thay thế kiểu empy_type thành đối tượng (value varchar2 (512)) 2. Create Type: tạo hoặc thay thế kiểu t_empty_type thành bảng của empy_type 3. Chức năng Create:

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;

Sau đó, chỉ cần gọi như thế này:

SELECT * FROM (TABLE(split('a,b,c,d,g'))) 
2 hữu ích 0 bình luận chia sẻ
loading
Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ oracle function plsql split delimiter , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm