CODEWITHSUNDEEP
sql-serverarraysoracle-databaseplsql
user2434
user2434

Reputation: 6399

Splitting string in oracle pl sql

I have a comma separated string that is passed to the stored procedure from the Java code.

For example

admin,role,user

Now how do I split this string and populate it into an array in oracle PL/SQL

The second question is , will this function be valid when we execute it in SQL server ?

Upvotes: 0

Views: 3906

Answers (4)

Allan
Allan

Reputation: 17429

While you can write functions in each database to parse your string, you risk violating the parser rules if new values are added in the future (in your case, imagine some decides to add "Admin, Super" as a user. The better way to do this is to use a structure designed for this purpose.

In Oracle, you could do the following:

create type Varchar10List as table of varchar(10);

create function YourFunction (pRoles Varchar10List) as
...

This wouldn't work for SQL Server though. SQL Server does have table-type parameters, which are similar, but they aren't supported by Java yet.

If you wanted to do it more generically, you could pass the list as very simple XML. Almost all database servers have integrated XML parsers at this point, so translating the XML into something more usable would be trivial, whether you're using Oracle or SQL Server.

Upvotes: 1

Elias Hossain
Elias Hossain

Reputation: 4469

Would you please try with below query:

--Creating function
CREATE FUNCTION GetSplittedData( @dataToSplit VARCHAR(500) )
RETURNS VARCHAR(500)
AS
BEGIN
    RETURN (''''+REPLACE(@dataToSplit, ',', ''',''')+'''')
END

--Calling function with data
SELECT DBO.GetSplittedData('admin,role,user')

Update: this is according to sql server

Upvotes: 0

Torres
Torres

Reputation: 5400

function str2tbl      (p_str in varchar2,p_delim in varchar2 default '.') return myTableType
  is
      l_str        long default p_str || p_delim;
       l_n        number;
       l_data     myTableType := myTabletype();
  begin
       loop
           l_n := instr( l_str, p_delim );
           exit when (nvl(l_n,0) = 0);
          l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+length(p_delim) );
     end loop;
     return l_data;
  end str2tbl;

Example of use: 

TYPE myTableType is table of varchar2(100);
v_array mytabletype;
v_array := str2tbl (string, ',');

Upvotes: 2

Stephen Turner
Stephen Turner

Reputation: 7314

On the SQL side, there is no native split function in T-SQL, but there is a good example of a CLR function that does the same by Erland Sommarskog here

It's unlikely that an Oracle function will be valid in SQL Server, the SQL statements usually needs conversion from one flavour to the other. However if your client is using a common driver such as odbc or .net then you might be able to shift this functionality into the client and have it work with both databases.

Upvotes: 0

Related Questions