Parsing of varchar2 fields
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Parsing of varchar2 fields

  1. #1
    Join Date
    Mar 2001
    Posts
    1

    Angry

    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





  2. #2
    Join Date
    Feb 2001
    Posts
    180
    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
    Regards
    Ben de Boer

  3. #3
    Join Date
    Apr 2001
    Posts
    14
    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.
    Anil
    anil_tj@yahoo.com

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,', ',');
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2001
    Posts
    14
    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?
    Anil
    anil_tj@yahoo.com

  6. #6
    Join Date
    Apr 2001
    Posts
    14
    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.
    Anil
    anil_tj@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width