Remove repeated string functions in Oracle stored procedures

The following functions are compiled inWrite OracleDatabaseStorageI believe that the functions written in the process will be very helpful to you. What is important is that the functions are quite versatile. Therefore, I would like to share them with you for your reference, I hope this will help you.

This function is mainly used to remove repeated strings in a given string. You need to specify the separator of the string in use. Example:

Str: = MyReplace ('13, 14, 13, 444 ',',');

Output:

444

Create or replace function MyReplace (oldStr varchar2, sign varchar2) return varchar2 is

Str varchar2 (1000 );

CurrentIndex number;

StartIndex number;

EndIndex number;

Type str_type is table of varchar2 (30)

Index by binary_integer;

Arr str_type;

Result varchar2 (1000 );

Begin

If oldStr is null then

Return ('');

End if;

Str: = oldStr;

CurrentIndex: = 0;

StartIndex: = 0;

Loop

CurrentIndex: = currentIndex + 1;

EndIndex: = instr (str, sign, 1, currentIndex );

If (endIndex <= 0) then

Exit;

End if;

Arr (currentIndex): = trim (substr (str, startIndex + 1, endIndex-startIndex-1 ));

StartIndex: = endIndex;

End loop;

Take the last string:

Arr (currentIndex): = substr (str, startIndex + 1, length (str ));

Remove the repeated strings:

For I in 1 .. currentIndex-1 loop

For j in I + 1 .. currentIndex loop

If arr (I) = arr (j) then

Arr (j): = '';

End if;

End loop;

End loop;

Str: = '';

For I in 1 .. currentIndex loop

If arr (I) is not null then

Str: = str | sign | arr (I );

Empty array:

Arr (I): = '';

End if;

End loop;

Remove the preceding identifier:

Result: = substr (str, 2, length (str ));

Return (Result );

End MyReplace;

Here, the explanation of removing the repeated string functions in the Oracle stored procedure is complete. I hope you can learn from the above.