sub_string extraction

Asmaa · #1 (**permalink**) 02-07-12, 09:16

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!

futurity · #2 (**permalink**) 02-07-12, 11:19

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.

bitznbitez · #3 (**permalink**) 03-21-12, 16:17

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech