Parsing of varchar2 fields

[damera]

I am new to this forum. But anyway I have serious question to ask about.

I have a table say Restaurant i.e.
Sql> desc restaurant
phone_number number(10);
cusine_type varchar2(20);

Phone number is the primary key for this table.
The data looks like this:

4082220010 1,2,3,4,5
4081111111 2,3,8,9

I have many cusine types which are identifed by id's
i.e. '1' stands for chinese
'2' stands for 'mexican'
'3' stands for 'italian' etc.
I have another table which keeps the cusinetype ids
i.e.
SQL>desc attributes
attribute_id number;
attribute_type varchar2(20);
This has the values like this;
1 chinese
2 mexican
3 italian
etc.
If I delete id 2 from attributes table, I have to delete 2 from
cusine_type string of restaurant. In my previous example
both restaurants has 2 in their cusine type.
So i have to delete 2 from cusine type and the data now becomes
4082220010 1,3,4,5
4081111111 3,8,9
So the real problem here is parsing for that particular value and then removing it from it.

Any inputs on this is greatly appreciated.

Thanks

[bensr]

Hi,
The only real good solution is to redefine the table(s):
1 table Restaurant (pk = phone_number)
2 table Cusine types (pk = cusine type)
3 table Restaurant_types (pk = phone number, cusine type)
and the foreign key relations Restaurant_fk, cusine_type_fk

Now, when you want to delete a cusine type
you have to remove first all items from Restaurant_types
and the date-integrity will remain

[iamanil]

i too would suggest you to change your table structure. but if you are so adamant about the existing table structure then in order to capture the values in the first field you can use a combination of INSTR and SUBSTR functions.

[jmodic]

Of course, the design is ugly and needs to be changed, but for the matter of removing part of the strings ther's no need to mess with INSTR and SUBSTR. There is a function REPLACE provided that does exactly what is needed here:

UPDATE restaurant SET cusine_type = REPLACE(cusine_type, ',2,', ',');

[iamanil]

jmodic. but there is a problem in using REPLACE. what about the '2's present within the phone number itself. that too would get replaced,right? so you have to find out whether '2's are present after the ',' (commas). this could be done only by using INSTR and SUBSTR, isn't it?

[iamanil]

hi jmodic. sorry for not going through your suggestion carefully. i missed out the commas along with the '2'. yeah it would definitely work and a much easier solution too.