split_part - split string in oracle select



Função Split no oracle para valores separados por vírgula com sequência automática (5)

Precisa da função Split, que terá dois parâmetros, string para dividir e delimitador, para dividir a string e retornar uma tabela com as colunas Id e Data.E como chamar a função Split, que retornará uma tabela com as colunas Id e Data. A coluna Id conterá a sequência e a coluna data conterá os dados da sequência. Por exemplo.

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

O resultado deve estar no formato abaixo:

|Id | Data
 --   ----
|1  | A  |
|2  | B  |
|3  | C  |
|4  | D  |

https://src-bin.com


Answer #1

Aqui está como você pode criar essa tabela:

 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;

Com um pouco de ajustes (ou seja, substituindo o , em [^,] por uma variável), você pode escrever essa função para retornar uma tabela.


Answer #2

Esta função retorna a enésima parte da sequência de entrada MYSTRING. O segundo parâmetro de entrada é o separador, por exemplo, SEPARATOR_OF_SUBSTR e o terceiro parâmetro é a Nª parte, que é necessária.

Nota: MYSTRING deve terminar com o separador.

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;

Espero que isso ajude algum corpo, você pode usar esta função como esta em um loop para obter todos os valores separados:

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;

Aqui NO_OF_RECORDS, PK_RECORD são variáveis ​​temporárias.

Espero que isto ajude.


Answer #3

Experimente como abaixo

select 
    split.field(column_name,1,',','"') name1,
    split.field(column_name,2,',','"') name2
from table_name

Answer #4

Se você precisar de uma função, tente isso.
Primeiro, criaremos um tipo:

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

Em seguida, criaremos a função:

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

Agora podemos selecionar:

select * from table(TEST_RETURN_TABLE()); 

Resultado:

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

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

6 rows selected.

Obviamente, você precisaria substituir o bit WITH TAB AS... por onde você obteria os dados reais. Credit Credit


Answer #5

Configuração do 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;
/

Inquerir

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

Saída :

ID DATA
-- ----
 1 A
 2 B
 3 C
 4 D




delimiter