Home » RDBMS Server » Server Administration » substr and instr functions
substr and instr functions [message #369687] Tue, 15 August 2000 15:36 Go to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
Hello everyone!
I have the following columns in a table called WAP:
address, city, zip, first_name, last_name.
my requirement is to insert these columns this way:
insert into WAP(first_name, last_name, address, city, zip)
then select with substr and instr in such a way that it that matches address, city, zip, first_name, last_name.
substr and instr is required to do this. I am having problem with it.
I have tried this:
SELECT SUBSTR (Data, INSTR(Data, ',', 4,1)+1, LENGTH(Data))"First_Name",
SUBSTR(Data, INSTR(Data, ',', 5,4)-4)"Last_Name",
SUBSTR(Data, INSTR(Data, ',', 1,1)+1, LENGTH(Data))"Address",
SUBSTR(Data, INSTR(Data, ',', 2,1)+1, LENGTH(Data))"City",
SUBSTR(Data, INSTR(Data, ',', 3,1), LENGTH(Data))"Zip"
FROM WAP;
This is not working. Can anyone help please?
thanks in advance.
sam
Re: substr and instr functions [message #369693 is a reply to message #369687] Wed, 16 August 2000 13:01 Go to previous messageGo to next message
Sam
Messages: 255
Registered: April 2000
Senior Member
Hi John!
Thank you for offering to help.
Here is what is required, followed by a sample code I have tried to write. Of course it did not work.
The column names are given in this order:
Address, City, Zip, First_Name, Last_Name.
"Data" is as input.

They are to be inserted into a table called WAP.
After inserting into a table, they should return these column names in this order:
First_Name, Last_Name, Address, City, Zip. Almost in reverse order using substr and instr.

This is my sample code.
SELECT SUBSTR (Data, INSTR(Data, ',', 4,1)+1, LENGTH(Data))"First_Name",
SUBSTR(Data, INSTR(Data, ',', 5,4)-4)"Last_Name",
SUBSTR(Data, INSTR(Data, ',', 1,1)+1, LENGTH(Data))"Address",
SUBSTR(Data, INSTR(Data, ',', 2,1)+1, LENGTH(Data))"City",
SUBSTR(Data, INSTR(Data, ',', 3,1), LENGTH(Data))"Zip"
FROM WAP;
This is not working.
Thanks again for offering to help!
sam
Re: substr and instr functions [message #369694 is a reply to message #369693] Wed, 16 August 2000 16:45 Go to previous messageGo to next message
JOHN
Messages: 182
Registered: April 1998
Senior Member
Sam,
One thing is you are not finding unique blocks of data within your string.
If I read your example correctly, this is the input string:

123 WEST AVE.,NEW YORK,99999-8888,JAMES,JONES

but you want the output to be:

JAMES,JONES,123 WEST AVE.,NEW YORK,99999-8888

Right?????

If the data is stored in a table with separate columns for each data element,
you only need to select them in the proper order.

If it is stored as one long comma delimited string in a column called 'DATA1' then
the following should work.

select substr(data1,instr(data1,',',1,3) +1,
length(data1)-instr(data1,',',1,4)) first_name,
substr(data1,instr(data1,',',1,4) + 1,
length(data1) - instr(data1,',',1,3 +1)) last_name,
substr(data1,1,instr(data1,',',1,1) -1) address,
substr(data1,instr(data1,',',1,1) +1,
(instr(data1,',',1,2) -1 - instr(data1,',',1,1) )) city,
substr(data1,instr(data1,',',1,2) +1,
(instr(data1,',',1,3) -1 - instr(data1,',',1,2))) zip
from jms1

I noticed that there is no value for STATE. Is this supposed to be an address?

Also you might want to substr your substr's to limit the length of the returned values
since substr returns a fairly long string by default.

I do not think this will be readable with the line length allowed in this message box. If you give me an e-mail address I will e-mail it directly to you.

John
Re: substr and instr functions [message #369705 is a reply to message #369687] Fri, 18 August 2000 18:40 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
Mastering instr and substr is certainly good, but see the following which uses Oracle's tekenizing.

CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS
mytable DBMS_UTILITY.uncl_array;
mylist VARCHAR2(80);
mytable_count NUMBER;
BEGIN
mylist := 'Alex, Donna, Hope, Jose, Judy, Julia, Nancy, Paul, Sandy';
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);
mylist := 'Empty.';
DBMS_OUTPUT.PUT_LINE('MYTABLE: ');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR item IN 1..mytable_count LOOP
DBMS_OUTPUT.PUT_LINE(mytable(item));
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_UTILITY.TABLE_TO_COMMA(mytable, mytable_count, mylist);
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
END;
/

begin
comma_sample;
end;
/
Previous Topic: sqlplus calls from pl/sql
Next Topic: To_Date with Null
Goto Forum:
  


Current Time: Sat Oct 31 21:03:45 CDT 2020