A bit long, but consulting docs & DuBois aren't yielding solutions apparant to me...
I'm converting a database where valuelists were controlled with a custom routine that read values from a text file. Selections of a specific valuelist were saved to a single field in the database, and each selection is delimited within the field with a \r. Hoping to use that same routine with MySQL, I imported this data into MySQL 3.23.46 and imported the valuelist selections as is into a VARCHAR field large enough to hold all selections. I'm fine storing selection values to the MySQL field. However, I now find myself in a pickle when it comes to searching that field for combinations of values. The previous database compared each value in my search string with each value as an independent "contains." So, if I had red, blue, green, black as selection choices, and a field had "red \r green \r black," (spaces are for readability only) I could search for "red \r black" and get a list of found records. MySQL doesn't like to do this. I can search with a single selection "red" and specify a 'contains' operator with my middleware (Lasso Pro 5) andget a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL searching with a literal "red \r black" single string, and finds no matches. Questions: 1 - is there a way with some SQL expression to search the existing field and \r delimited data with a string such as "red \r black" to reproduce the same search I had before? (I know this will not be as fast as SET). 2 - if I convert this column to a SET and define my choices, will MySQL automatically recognize the existing \r delimited values in the fields and properly convert the field data? 2a - do I first have to manually convert all \r instances to commas or something else? 2b - do I have to export the data, redefine the column as SET, then reimport it into this column? 3 - what happens to field data when the SET selections are redefined? The reason behind the previous setup was to allow easy changes to the selection list and isolate the definition of the list from the database. I know SET is faster, but these are really small databases. Thanks. -- Greg Willits -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
