I'm converting a database where multiple selections from a valuelist were stored delimited within a single field with a \r.
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 using the existing middleware valuelist routines. 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. In MySQL I can search with a single selection "red" and specify a 'contains' operator with my middleware (Lasso Pro 5) and get a list of records. However, I cannot include more than one option in my search string. It's obvious (and understandable) that MySQL is searching with a literal "red \r black" 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 to its own preferred format for SET fields? 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
