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
Messages: 12
Registered: May 2006
Junior Member

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


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
Messages: 25523
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
Messages: 1844
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
Previous Topic: Send email with attachement through PL/SQL
Next Topic: ORA-01007: variable not in select list
Goto Forum:

Current Time: Thu Jul 20 19:44:07 CDT 2017

Total time taken to generate the page: 0.11067 seconds