Home » SQL & PL/SQL » SQL & PL/SQL » substring problem
substring problem [message #234849] Thu, 03 May 2007 05:47 Go to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
hi experts

i have to perform a task of seperating a single code to two different codes..the tables are as follows


create table route_master(route_id number primary key,
route_desc varchar2(20));

create table route_plan(plan_id primary key,
route_id number,
source varchar2(20),
destination varchar2(20));


route_master values,
route_id route_desc
1 trk-chennai-mumbai
2 trk-chennai-bangalore

my problem is whenever the user selects the route_id =1 during the data entry of the route_plan..'chennai' has to be stored in source and 'mumbai' gas to be stored in destination of the route_plan table
please help me out
thanks in advance
Re: substring problem [message #234855 is a reply to message #234849] Thu, 03 May 2007 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And where "trk" must be store?

I never hear that a select also makes an insert but who kows...

Regards
Michel
Re: substring problem [message #234861 is a reply to message #234849] Thu, 03 May 2007 06:10 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks for ur reply.

what i wanted to know is,

how to sepearte 'chennai' and 'mumbai' seperately from 'trk-chennai-mumbai'..i tried using substr,but coul'nt get what i wanted..please help me out..
thanks in advance
Re: substring problem [message #234885 is a reply to message #234861] Thu, 03 May 2007 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you tried.
You may also need "instr" function.

Regards
Michel
substring problem [message #234908 is a reply to message #234849] Thu, 03 May 2007 07:53 Go to previous messageGo to next message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
i am able to get 'mumbai'


select substr(
substr('trk-chennai-mumbai',instr('trk-chennai-mumbai','-')+1),
instr(
substr('trk-chennai-mumbai',instr('trk-chennai-mumbai','-')+1),'-')+1)
from dual;



but i am unable to get the 'chennai'.can u help me out
Re: substring problem [message #234924 is a reply to message #234908] Thu, 03 May 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as ( select 'trk-chennai-mumbai' str from dual )
  2  select substr(str, instr(str,'-')+1, instr(str,'-',1,2)-instr(str,'-')-1) str1,
  3         substr(str, instr(str,'-',1,2)+1) str2
  4  from data
  5  /
STR1    STR2
------- ------
chennai mumbai

1 row selected.

Regards
Michel
Re: substring problem [message #234930 is a reply to message #234924] Thu, 03 May 2007 08:44 Go to previous message
karthick2809
Messages: 50
Registered: April 2007
Location: Chennai
Member
thanks michel....its working....
Previous Topic: How to search a string value irrespective of case sensitive
Next Topic: Queries regarding Associative Arrays (merged topics)
Goto Forum:
  


Current Time: Fri Dec 09 07:51:28 CST 2016

Total time taken to generate the page: 0.11968 seconds