how to split strings in pl/sql or in sql/plus

baka · *nix forums beginner Joined: 06 Jun 2005 Posts: 23

hello
I want to break the the follwoing string variable into two strings.

eg; str="abcd\wxyz"
into
str1="abcd"
str2="wxyz"

i know how to do this in all 4GLs .
is it possible to do it in SQL/PLUS or PL/SQL?
(by the way i am re3ading a datfile from a .csv)

Thanks in Advance

Chris ( Val ) · *nix forums addict Joined: 18 Apr 2005 Posts: 99

baka wrote:

Mladen Gogala · *nix forums Guru Joined: 11 Mar 2005 Posts: 948

On Mon, 06 Jun 2005 17:35:16 -0700, baka wrote:

baka · *nix forums beginner Joined: 06 Jun 2005 Posts: 23

Thank you very much for the reply.
I have a java program to all this by utilizing Token concept.

My constraint to perform this was using only SQL/PLUS or PL/SQL which
are
readily available on the server/client.
for perl i have to install al that package...(can be done in one line)
is it any way i can do it in a simple way.
Thanks

baka · *nix forums beginner Joined: 06 Jun 2005 Posts: 23

Hello Oracle guru's
Sorry for not providing the details

here is my java program.
is it ppossible to write this kind of stuf in PL/SQL.
let me have the sample if possible.
the below java (as it is i.e minus JDBC connection) works in eclipse
environment.

Thanks in Advance
------------------------------------------------------------------------
// My first java program for this kind
// mesun@japan............
// 2005/06/07
//import java.sql.*;
import java.io.*;
import java.util.*;
public class strpMinjdbc {
public static void main(String[] args) {
String line, sql = "";
//Connection con = null;
//String driver = "com.oracle.jdbc.xxxDriver";
// String url = "jdbc:oracle:thin:@xxhost:1521:xxxDB" ;
// String user = "scott";
//String password = "tiger";

try {
//Class.forName(driver);
//con = DriverManager.getConnection(url,user,password);
FileReader filereader = new FileReader("c:/jnew/yourfile.csv");
BufferedReader bufferreader = new BufferedReader(filereader);
// writing to a file
FileOutputStream out; // declare a file output object
PrintStream p; // declare a print stream object
out = new FileOutputStream("c:/jnew/myfile.txt");
p = new PrintStream( out );

//Here i am skipping the header data from the log file
line = bufferreader.readLine();
//java.sql.Statement stmt = con.createStatement();
//co.setAutoCommit(false);
while ((line = bufferreader.readLine()) != null) {
//System.out.println("orginalline Line:" +line);
//sql = "INSERT INTO logdata (c1,c2,c3,c4,c5) values(";
StringTokenizer rdLine = new StringTokenizer(line, ",");
sql = "";
int i = 0;
while (rdLine.hasMoreTokens()) {
i++;
//System.out.println("SQL:" + sql);
String Tokendata = rdLine.nextToken();
if (i != 6) {
if (i == 2) { // here i go for spliting the strings
String searachme="\\";
int strposn=Tokendata.indexOf(searachme)+1;
String sql2 =Tokendata.replace('\\',',');
sql = sql+ sql2 + ",";
//System.out.println("ffff:" + sql2);
}
else
sql = sql + Tokendata + ",";

} else {
sql = sql + Tokendata + ")";
break;
//System.out.println("records:" + sql);
}
//i++;
}
System.out.println("records:" + sql);
p.println (sql);

//stmt.executeUpdate(sql);
}
//con.commit();
filereader.close();
p.close();

//con.close();
} catch (Exception e) {
//System.out.println("SQL:"+sql);
e.printStackTrace();
}
}
}
--------------------------------------------------------------------------

Jeremy · *nix forums Guru Wannabe Joined: 26 Apr 2005 Posts: 228

In article <1118104516.241609.275800@g43g2000cwa.googlegroups.com>, baka
says...

Rauf Sarwar · *nix forums Guru Joined: 03 May 2005 Posts: 353

baka wrote:

baka · *nix forums beginner Joined: 06 Jun 2005 Posts: 23

Hello
Thank you all for your simple and short and meaningfull replies.
Your all just great people on this earth.
Just a note say that i used StringTokenizer() because i can handle
variable in a simple way. and Split functionctionality is an recent
addition to java(from 1.4.xx on-wards)

Thanks all and and i will come for your helps again some time later.

David Sykes · *nix forums beginner Joined: 09 Jun 2005 Posts: 1

Here's some pl/sql code I used a few years ago:

set serveroutput on;

declare

components owa_text.vc_arr ;

string1 varchar2(200) default '06/13/2005';

begin

if (owa_pattern.match(string1, '(\w*)/(\w*)/(\w*)', components)) then

for counter in 1 .. components.count loop
dbms_output.put_line(components(counter));
end loop;

end if;
end;
/

this works in 9i (9.2) here is the output:

QL*Plus: Release 8.1.7.0.0 - Production on Thu Jun 9 15:47:58 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

06
13
2005

PL/SQL procedure successfully completed.

Dave

baka wrote:

baka · *nix forums beginner Joined: 06 Jun 2005 Posts: 23

Hello Mr.Davis Sykes
cc: to wonderful reader and repliers

O! this is great, you are showing me how to do perl kind of stuf in
PL/SQL
Wonderful!!!, i will take your sample with your permission.

One last question
Does Oracle 10G is bundled (Machine level ) with perl?(Joke!!.
As oracle 10 G is added with Regular expression.)

Thnaks again

David Sykes ã®ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸:

ptop · *nix forums beginner Joined: 26 Aug 2009 Posts: 1

My situation is similar but little complicated.
I'll have a string ('12-34,23-45,34-56,45-67')
Now i have to drive values from pl/sql like
12-34
23-45
34-56
45-67
Help is highly appriciated

Google