I have a table with user defined columns (the table can be extended to many columns). It would be great if a user could search for 'Ayn Rand' and get rows that match for any column containing '%Ayn Rand%'.
My hope is that full text search gives me a better solution than this...
WHERE col1 like '%Ayn Rand%' OR col2 like '%Ayn Rand%' OR col3 like '%Ayn Rand%'
The perfect situation would be...
WHERE CONTAINS '"Ayn Rand"'
I'm guessing it will require too much text index refreshing and maintenance to be useful but I'm clinging to hope.
Thank you for any insights.
asked 04 Jun '10, 16:52
You should look at your table structures. Instead of letting users define their own columns, consider letting them define types of records. They would store all the records, of various user-named types, in a table with the data in one column and an indicator of the record type in another column. The list of user-named record types would be in a separate table.
answered 06 Jun '10, 06:35