Searching Text field full of '|' seperated numbers, how?

[CalDude]

Hi,
  In my MySQL 4 DB I have a text field that looks something like this:  '15|16|44|140|148|154|165|169|170|180|184|185|189'.  Many numbers separated my a '|' character, each number corresponds to a feature the user may wish to search for.  So, I will have a set of numbers like: 13,15,140,144,156, and I want the DB to return the rows with at least one of those numbers present in the text field.  Sorted by relevance, with the rows that contain more of the numbers first.  I can index this field if necessary.  How can I accomplish this?

Thanks!

[fenway]

Well, you can probably cheat, REPLACE() all the pipes with commas, and then use FIND_IN_SET().

[btherl]

If you don't mind redesigning your table, you can change from

thing varchar,
features varchar,
Unique index on thing

to

thing varchar,
feature integer,
Non-unique index on thing (for use when updating a thing's features)
Non-unique index on feature (for searching by feature)

Then you can simply SELECT distinct thing FROM table WHERE feature IN (13,15,140,144,156);

[fenway]

Or do it really properly and normalize the table so that you don't store lists in column values.