Hello how are you? Could you help me convert this function from sql server to oracle. i'm still new to oracle and I need the function to separate a url by backslah Thanks a lot

create FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string   @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos   1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos   1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos   @len)   1
    END

    RETURN
END

I tried to convert the function in this way, but I can't get it to compile and it gives me errors that I don't understand how to solve

I have the error in this part RETURN out_put table(

CREATE OR REPLACE FUNCTION fn_split_string_to_column (
    p_string VARCHAR2,
    p_delimiter VARCHAR2
    )
RETURN  out_put table(
    column_id
    End; INT ROWNUM1, 1) NOT NULL,
    valores VARCHAR(2000)
    )
AS
BEGIN
    v_value VARCHAR2(2000);
        v_pos NUMBER(10) := 0;
        v_len NUMBER(10) := 0

    v_string := CASE 
            WHEN SUBSTR(string, GREATEST(-LENGTH(string), -1)) != delimiter
                THEN string   delimiter
            ELSE string
            END

    WHILE INSTR(string, delimiter, v_pos   1) > 0
    LOOP
        v_len := INSTR(string, delimiter, v_pos   1) - v_pos
        v_value := SUBSTR(string, v_pos, v_len)

      
        INSERT INTO out_put (valores)
        SELECT LTRIM(RTRIM(v_value)) AS columna FROM dual;

        v_pos := INSTR(string, delimiter, v_pos   v_len)   1
    END LOOP

    RETURN
END

CodePudding user response：

Adapting my previous answer:

CREATE TYPE indexed_string AS OBJECT(
  column_id NUMBER,
  value     NVARCHAR2(4000)
);

CREATE TYPE indexed_string_list AS TABLE OF indexed_string;

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  NVARCHAR2,
  i_delim  IN  NVARCHAR2 DEFAULT ','
) RETURN indexed_string_list PIPELINED DETERMINISTIC
AS
  p_index        PLS_INTEGER := 0;
  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_index := p_index   1;
      PIPE ROW (
        indexed_string(
          p_index,
          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_index := p_index   1;
      PIPE ROW (
        indexed_string(
          p_index,
          SUBSTR( i_str, p_start, c_len - p_start   1 )
        )
      );
    END IF;
  END IF;
END;
/

Then:

SELECT *
FROM   TABLE(split_string(N'abc\def\\ghi\jkl', '\'))

Outputs:

COLUMN_ID	VALUE
1	abc
2	def
3	null
4	ghi
5	jkl

db<>fiddle here

CodePudding user response：

You do not need a function, you can do it with a recursive sub-query factoring clause and simple string functions:

WITH bounds (value, idx, lpos, epos) AS (
  SELECT value,
         1,
         1,
         INSTR(value, '\', 1)
  FROM   table_name
UNION ALL
  SELECT value,
         idx   1,
         epos   1,
         INSTR(value, '\', epos   1)
  FROM   bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY value SET order_id
SELECT value,
       idx,
       CASE epos
       WHEN 0
       THEN SUBSTR(value, lpos)
       ELSE SUBSTR(value, lpos, epos - lpos)
       END AS item
FROM   bounds;

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT N'abc\def\\ghi\jkl' FROM DUAL UNION ALL
  SELECT NULL FROM DUAL UNION ALL
  SELECT N'\' FROM DUAL;

Outputs:

VALUE	IDX	ITEM
\	1	null
\	2	null
abc\def\ghi\jkl	1	abc
abc\def\ghi\jkl	2	def
abc\def\ghi\jkl	3	null
abc\def\ghi\jkl	4	ghi
abc\def\ghi\jkl	5	jkl
null	1	null

db<>fiddle here