Hello all, I need help on solving this problem.

I have a table "Table A" with ID and Items Fields. ID is unique. Items Filed contains Part numbers with ';' dilimited.

ID | Items
1 | AFF-12D; ACC-15S;
2 | ACC-15S;
2 | KXX-1129;

How can I select rows with ACC-155. How can i do it?

Thanks in advance.

You can do something like:

Select * from A where Items like '%ACC-155%'

yupe.... that is the best way..

Metal and Roll... Thanks UZZO.

Well, there are a couple of problems.

#1. Putting a delimited list in a field is not a good database design practice. In fact, to be honest, it is a BAD design. It doesn't even qualify the 1st normal form of the Relational Database Theory. You gotta break it down into multiple tables. Example:

TABLE PART:

ID PART_DESC ... ...
1 My Part 1
2 My Part 2 ... ...
3 My Part 3 ... ...

TABLE PART_CODE:

PART_ID CODE
1 AFF-12D
1 ACC-15S
2 ACC-15S
3 KXX-1129

#2. The SQL uzzo suggested has a problem that may not appear at first, but will surface sooner or later. (and your pager will vibrate at 2AM -;)). The problem is that:

Select * from A where Items like '%ACC-155%'

Will not only select ACC-155, but also:

mACC-155n

where:
m = one or more (any) characters.
n = one or more (any) characters.

Examples:
KAAC-1558
RAAC-155z9
..and all other infinite possibilities.

Again, it may not cause problems now because you don't have enough data, but you should think ahead...

So the solution is simple if you redesign your database the way I suggested (and that's the way it should be). Your SQL will simply look like:

Select * from PART, PART_CODE where PART.ID = PART_CODE.PART_ID AND PART_CODE.CODE = 'ACC-155'

That will work 100% of the time.

But if you must keep (please don't!) your existing design, then at least rewrite your SQL as this:

Select * from A where (Items = 'ACC-155') OR (Items like 'ACC-155;%') OR (Items like '%;ACC-155;%') OR (Items like '%;ACC-155')

(That's to make sure that you don't select a wrong item just because part of the code matches, yet take care of all possible cases how a valid item code of 'ACC-155' may exist in the field.)

I hope you can see the difference. See it gets ugly and error prone. That's why you need to follow the relational theory when you do the design and normalize your data. It's there for a reason ni!