Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    10

    Unhappy Unanswered: sub_string extraction

    Hi
    I have two tables,

    First one has the following contents:

    Code:
    CREATE TABLE A
    (
    FCode VARCHAR NOT NULL,
    VarietyName VARCHAR
    PRIMARY KEY (FCode)
    );
    INSERT INTO A
    VALUES
    (
    'AP01',
    'Bramley Cookers'
    );
    INSERT INTO A
    VALUES
    (
    'PR01',
    'Comice'
    );
    Second one has the following contents:

    Code:
    CREATE TABLE B
    (
    typeOfFruit_character CHAR(2) PRIMARY KEY,
    Type VARCHAR
    );
    INSERT INTO B
    VALUES
    (
    'AP',
    'Apples'
    );
    INSERT INTO B
    VALUES
    (
    'PR',
    'Pears'
    );
    I want to use sub_string extraction feature to answer the following query:
    (Assume that AP01 and PR01 are to kind of fruits)
    *what is the type of fruit AP01?
    I want to write a code that when I write the first two characters of “FCode”( for example AP ) from table A I get the type of fruit from table B . in this case Apples.
    Please help me. I have no idea how to do it!

  2. #2
    Join Date
    May 2008
    Posts
    277
    Whenever you are trying to extract a subset of information from one of your columns to answer a question, a big horn should go off in your head that you're doing something wrong and that your table is not normalized.

    Every column in your table should hold exactly one fact. Your FCode column holds two: the type of food it is ("an apple"), and an identifier of its variety ("variety 1"). Split that into two columns and your problem goes away. Additionally, you'll be able to enforce referential integrity on the food type using a foreign key to Table B.

  3. #3
    Join Date
    Mar 2012
    Posts
    10
    I made an assumption about how tables a and b are related ..........

    select type from a, b
    where
    a.substring(a.FCode,1,2) = b.typeOfFruit
    and
    a.substr(a.FCode,1,2) = 'AP'
    ;

    Should do what you want.

Posting Permissions

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