Home » SQL & PL/SQL » SQL & PL/SQL » Help with String (Instr)
Help with String (Instr) [message #226883] Mon, 26 March 2007 18:03 Go to next message
sudkool
Messages: 12
Registered: May 2006
Junior Member
Hi,

I have following scenario and I was just wondering if someone could help me as I am fairly new in DB field.

Table1.column1 have all treatment codes like

--Table 1 . Column 1 values

01
12345
4321
6656
99898
220

and then I have a look up table (lets say Table2) in which I have a field with 72 character long which have random treatment codes like

---- TABLE 2 . Column1 values--
1111, 2121,221, 2203, 0134 (row 1)
43210, 0110 (row 2)
02201 (row 3)

I have to pull Table2.detail field where Table1.column1 find a match in Table2.column2. I was using INSTR () function but it is not working and I am facing problem like Table1.Column1 's value 01 find match in row 2 and row 3 of table2.coumn2 which is not a right scenario.

Could someone please help me how to handle problems like that?

Thank you
Re: Help with String (Instr) [message #226902 is a reply to message #226883] Mon, 26 March 2007 21:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Those who live with by/with a non-normalized "design", will long time suffer for the design foolishness.

It is a bad, bad, bad design to store multiple comma separated values in a single field.
Now you have learned 1st hand the complications which happen when the design does not meet 3rd Normal Form.

I doubt that the requirements can met with SQL only; you'll likely have to use PL/SQL.
You're On Your Own(YOYO)!

[Updated on: Mon, 26 March 2007 21:07] by Moderator

Report message to a moderator

Re: Help with String (Instr) [message #227005 is a reply to message #226883] Tue, 27 March 2007 02:01 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Agree with anacedent, wrong design, if stored in DB this way.

You can not use 'simple' INSTR then, you shall split the varchar into values and treat as a set you are searching in.
Either do it by stored procedure described on AskTom or try the SQL alternatives described eg. here or
here.
Previous Topic: Send email with attachement through PL/SQL
Next Topic: ORA-01007: variable not in select list
Goto Forum:
  


Current Time: Fri Dec 09 15:16:08 CST 2016

Total time taken to generate the page: 0.15819 seconds