CalDude Posted November 6, 2006 Share Posted November 6, 2006 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! Link to comment https://forums.phpfreaks.com/topic/26270-searching-text-field-full-of-seperated-numbers-how/ Share on other sites More sharing options...
fenway Posted November 6, 2006 Share Posted November 6, 2006 Well, you can probably cheat, REPLACE() all the pipes with commas, and then use FIND_IN_SET(). Link to comment https://forums.phpfreaks.com/topic/26270-searching-text-field-full-of-seperated-numbers-how/#findComment-120439 Share on other sites More sharing options...
btherl Posted November 7, 2006 Share Posted November 7, 2006 If you don't mind redesigning your table, you can change fromthing varchar,features varchar,Unique index on thingtothing 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); Link to comment https://forums.phpfreaks.com/topic/26270-searching-text-field-full-of-seperated-numbers-how/#findComment-120761 Share on other sites More sharing options...
fenway Posted November 7, 2006 Share Posted November 7, 2006 Or do it really properly and normalize the table so that you don't store lists in column values. Link to comment https://forums.phpfreaks.com/topic/26270-searching-text-field-full-of-seperated-numbers-how/#findComment-120897 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.