Home » RDBMS Server » Server Administration » substr and instr functions
substr and instr functions [message #369687] |
Tue, 15 August 2000 15:36 |
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 |
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 |
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 |
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;
/
|
|
|
Goto Forum:
Current Time: Sat Sep 28 17:02:25 CDT 2024
|