Home » SQL & PL/SQL » SQL & PL/SQL » Some String operations..
Some String operations.. [message #189056] Wed, 23 August 2006 01:16 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a string which can have 1 to 3 words separated by comma. Examples:

1) ABC_and_XYZ,Dependent_and_HUGH,FHY_and_JUTE

2) DESSED_JYT_09PRTY

3) JUDGU_HDR_PO567Y,PGURHY_90&TY_MIUTY

The string is the input. Based on the examples above, I need to output something like:

1) XYZ_and_ABC,HUGH_and_Dependent,JUTE_and_FHY

2) 09PRTY_JYT_DESSED

3) PO567Y_HDR_JUDGU,MIUTY_90&TY_PGURHY

The output is switching the first and third words before the first underscore (_) and the second underscore (_)..
Help from anyone?
Re: Some String operations.. [message #189057 is a reply to message #189056] Wed, 23 August 2006 01:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Are you looking for a pure (but more complex) SQL solution or a PL/SQL function?

MHE
Re: Some String operations.. [message #189062 is a reply to message #189057] Wed, 23 August 2006 01:44 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I prefer SQL.. For PL/SQL, I think I don't need much help..
Re: Some String operations.. [message #189069 is a reply to message #189062] Wed, 23 August 2006 02:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
a_developer wrote on Wed, 23 August 2006 08:44

For PL/SQL, I think I don't need much help..
Sure.

So you have a string that you want to transform in the following way: each string is divided in parts by a comma. The order of these parts needs to be preserved. But each part is again divided by an underscore. The order of these "sub-parts" needs to be reversed. But I also see you want to reverse in case of an ampersand (&). Am I correct so far? Are there other characters that separate these "sub-parts"?

MHE

[Updated on: Wed, 23 August 2006 02:02]

Report message to a moderator

Re: Some String operations.. [message #189071 is a reply to message #189069] Wed, 23 August 2006 02:07 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Just first separate by comma, and then by underscore.. I think I need lots of nested SUBSTR/INSTR.. but the thing is that the number of strings separated by commas may vary from 1 to 3..
Re: Some String operations.. [message #189082 is a reply to message #189071] Wed, 23 August 2006 02:40 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd go for a function. You can do it in SQL but it would be quite some select. Here's an example how to split a string in SQL. You need to nest that since you want to separate the string, change the order and concatenate it back again.

A function would make things a lot easier IMO.

MHE
Re: Some String operations.. [message #189089 is a reply to message #189082] Wed, 23 August 2006 03:09 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
i think i'd go for the simple solution: function...
Re: Some String operations.. [message #189094 is a reply to message #189089] Wed, 23 August 2006 03:32 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Yes. Function is the good choice in this scenario, because in the SQL itself achieving the same is complicated.

check the below functions...
SQL> select * from str_test;

STR
--------------------------------------------------------------------------------
ABC_and_XYZ,Dependent_and_HUGH,FHY_and_JUTE
DESSED_JYT_09PRTY
JUDGU_HDR_PO567Y,PGURHY_90&TY_MIUTY

create or replace function str_test_fun(str varchar2)
return varchar2
is 
v_str varchar2(200);
res_str varchar2(200);
v_start number;
v_end number;
begin
v_start := 1;
v_end := 0;

for i in 1..length(str)-length(replace(str,',',''))+1
loop

v_end := instr(str,',',1,i);
if v_end = 0 then
v_str := substr(str,v_start,length(str)-v_start+1);
else
v_str := substr(str,v_start,v_end-v_start);
end if;

res_str := res_str|| substr(v_str,instr(v_str,'_',1,2)+1)||substr(v_str,instr(v_str,'_',1,1),(instr(v_str,'_',1,2)-instr(v_str,'_',1,1))+1)||substr(v_str,1,instr(v_str,'_',1,1)-1)||',';

v_start := v_end+1;
end loop;

return rtrim(res_str,',');
end;
/

SQL> select str_test_fun(str) from str_test;

STR_TEST_FUN(STR)
--------------------------------------------------------------------------------
XYZ_and_ABC,HUGH_and_Dependent,JUTE_and_FHY
09PRTY_JYT_DESSED
PO567Y_HDR_JUDGU,MIUTY_90&TY_PGURHY


Thanks,
Thangam
Previous Topic: How to call a URL and read what it returns?
Next Topic: file reading and writing
Goto Forum:
  


Current Time: Thu Dec 08 18:24:54 CST 2016

Total time taken to generate the page: 0.08995 seconds