Home » SQL & PL/SQL » SQL & PL/SQL » Breaking down a string into parts
Breaking down a string into parts [message #212399] Fri, 05 January 2007 03:03 Go to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
This is a sort of 're-engineering'; a string needs to be cut up into parts again. The string contains a code for a department/office/unit/subunit but needs to be separated into seperate columns.
Example: 'MS\ S&I\ CMC\ CAB\Bi'
Result should be:
Dir='MS', Dept='S&I', Subdept='CMC' Unit='CAB', SubUnit='Bi'
Notice the spaces, backslashes, '&'...

Can this be done?
icon2.gif  Re: Breaking down a string into parts [message #212405 is a reply to message #212399] Fri, 05 January 2007 03:47 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
yes it can be done.ever heard of instr and substr?
Re: Breaking down a string into parts [message #212408 is a reply to message #212405] Fri, 05 January 2007 03:54 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Yes, have heard of instr/substr but give me a hint to use it for this particular example. The strings have no fixed lengths, I just need the individual code for dept/office etc.
I was trying it with PL/SQL but I thought there would be an easier solution..
Re: Breaking down a string into parts [message #212412 is a reply to message #212408] Fri, 05 January 2007 04:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
The strings have no fixed lengths

To locate some character we use instr.

By
Vamsi
Re: Breaking down a string into parts [message #212416 is a reply to message #212399] Fri, 05 January 2007 04:35 Go to previous messageGo to next message
vijaykasi
Messages: 11
Registered: January 2007
Location: London
Junior Member
CREATE OR REPLACE function find_position(src varchar2,pos number,del varchar2) return varchar2 is
i number:=0;
ipos number:=0;
ipos1 number:=0;
begin
while (i<pos) loop
ipos1:=ipos;
ipos:=instr(src,del,ipos+1);
i:=i+1;
end loop;
IF (ipos=0) THEN
return substr(src,ipos1+1);
ELSE
return substr(src,(ipos1+1),(ipos-ipos1));
END IF;
end;
/

Now you can call this function as below,
find_position(<src_string>,<which_position>,<delimeter_string>) will return you the actual characters., For example,

declare
i varchar2(2000):='MS\ S&I\ CMC\ CAB\Bi';
begin
dbms_output.put_line('first='||find_position(i,1,'\'));
dbms_output.put_line('second='||find_position(i,2,'\'));
dbms_output.put_line('third='||find_position(i,3,'\'));
dbms_output.put_line('fourth='||find_position(i,4,'\'));
dbms_output.put_line('fifth='||find_position(i,5,'\'));
end;
/

Remember if you have & in input (example S&I),and when you call this function from pl/sql it expects a value to continue, so try to handle input info when calling this function
Re: Breaking down a string into parts [message #212419 is a reply to message #212399] Fri, 05 January 2007 04:40 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Hi,
Try this. I hope this will help you.

create table tmp (str varchar2(100));

insert into tmp values ('MS\ S&'||'I\ CMC\ CAB\Bi');

insert into tmp values ('Test_Dir\Test_Dept\Test_Subdept\Test_Unit\Test_SubUnit');

insert into tmp values ('Test Dir\Test Dept\Test Subdept\Test Unit\Test SubUnit');

select * from tmp;

str
------------------------------------------------------
MS\ S&I\ CMC\ CAB\Bi
Test_Dir\Test_Dept\Test_Subdept\Test_Unit\Test_SubUnit
Test Dir\Test Dept\Test Subdept\Test Unit\Test SubUnit


select
substr(str,1,instr(str,'\',1,1)-1) Dir,
substr(str,instr(str,'\',1,1)+1,instr(str,'\',1,2)-instr(str,'\',1,1)-1) Dept,
substr(str,instr(str,'\',1,2)+1,instr(str,'\',1,3)-instr(str,'\',1,2)-1) SubDept,
substr(str,instr(str,'\',1,3)+1,instr(str,'\',1,4)-instr(str,'\',1,3)-1) Unit,
substr(str,instr(str,'\',1,4)+1) SubUnit
from tmp;


dir dept subdept unit subunit
---------------------------------------------------------------------
MS S&I CMC CAB Bi
Test_Dir Test_Dept Test_Subdept Test_Unit Test_SubUnit
Test Dir Test Dept Test Subdept Test Unit Test SubUnit


Regards,
Anil
Re: Breaking down a string into parts [message #212422 is a reply to message #212419] Fri, 05 January 2007 04:43 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use 'trim' on top of that to remove the spaces off.

By
Vamsi
Re: Breaking down a string into parts [message #212474 is a reply to message #212399] Fri, 05 January 2007 08:47 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Thanks for all the suggestions but it didn't really work for me as the strings may contain 1 thru 5 delimeters '(backslashes).

Here's what I made myself and it works:
-------------------------------------------
DECLARE
cursor c_lvnl is
select * from MYTABLE;
--
l_string varchar2(100);
l_dir varchar2(20);
l_afd varchar2(20);
l_onderafd varchar2(20);
l_unit varchar2(20);
l_subunit varchar2(20);
--
BEGIN
--
FOR i in c_lvnl LOOP
l_string := i.lvnl;
l_dir := '';
l_afd := '';
l_onderafd := '';
l_unit := '';
l_subunit := '';
--
l_dir := substr(l_string, 1, instr(l_string,'\',1,1) - 1);
if l_dir is NOT NULL then
l_string := trim( substr(l_string, (instr(l_string,'\',1,1) + 1 ) ));
l_afd := substr(l_string, 1, instr(l_string,'\',1,1) - 1);
if l_afd is NOT NULL then
l_string := trim( substr(l_string, (instr(l_string,'\',1,1) + 1 ) ));
l_onderafd := substr(l_string, 1, instr(l_string,'\',1,1) - 1);
if l_onderafd is NOT NULL then
l_string := trim( substr(l_string, (instr(l_string,'\',1,1) + 1 ) ));
l_unit := substr(l_string, 1, instr(l_string,'\',1,1) - 1);
if l_unit is NOT NULL then
l_string := trim( substr(l_string, (instr(l_string,'\',1,1) + 1 ) ));
l_subunit := trim(l_string);
else l_unit := l_string;
end if;
else l_onderafd := l_string;
end if;
else l_afd := l_string;
end if;
else l_dir := l_string;
end if;
--
update MYTABLE
set directoraat = l_dir
, afd = l_afd
, onderafd = l_onderafd
, unit = l_unit
, subunit = l_subunit
where org_id = i.org_id;

END LOOP;
END;
Re: Breaking down a string into parts [message #212477 is a reply to message #212474] Fri, 05 January 2007 08:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What version of the database are you using?
Re: Breaking down a string into parts [message #212483 is a reply to message #212399] Fri, 05 January 2007 09:14 Go to previous messageGo to next message
pinhit
Messages: 10
Registered: November 2006
Junior Member
Version of Database is important?
I'm using 8i (testenvironment) and 9i (prod)
Re: Breaking down a string into parts [message #212512 is a reply to message #212483] Fri, 05 January 2007 11:13 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
pinhit wrote on Fri, 05 January 2007 10:14
Version of Database is important?
I'm using 8i (testenvironment) and 9i (prod)


Of course database version is important. Things do change !

Different versions of the database between TEST & PROD ? Never heard of that ??

This will do for 9i:

with t as (
	select '\'||string||'\' str from temp_slash)
select trim(substr(str, instr(str, '\', 1, rownum) + 1,
	instr(str, '\', 1, rownum + 1) - instr(str, '\', 1, rownum) -1)) Parts
from t
connect by level < length(str) - length(replace(str, '\'));


And if you were on 10g, you could consider regex or some of srinivnp's vodoo with the MODEL clause.

[Updated on: Fri, 05 January 2007 11:24]

Report message to a moderator

Previous Topic: NOT EQUAL QUESTION
Next Topic: find out second sunday in March
Goto Forum:
  


Current Time: Wed Dec 07 12:31:00 CST 2016

Total time taken to generate the page: 0.18477 seconds