April 24, 2006 at 1:09 am
I have a table with, say, patients.
In that table I have multiple records for each patient with a memo field on it. Is it possible to filter patients that have records containing some text.
For example, I need to know how many patients that in the memo field of one of his records contains "headache", and in the memo field of another record of the same patient contains "aspirin" on it.
April 24, 2006 at 6:18 am
Have you tried...
select patientName, memoCol from myTable where (memoCol like '%headache%') or (memoCol like '%aspirin%') order by patientName?!
**ASCII stupid question, get a stupid ANSI !!!**
April 24, 2006 at 7:00 am
If this is going to be a popular re-runnable query, a quicker response will be gained by using another idea.
Create a linked table from the source table which includes patientID and memoKeyWord and put indexes on both. Then populate this new table by inserting the each word in the memotext column and patientid from the source, by looping through all the records. Admittedly, it will take a while, but once you populate it, you can then update it nightly.
Once you have this, you can query the new table for the exact words making full use of indexes. It will be a lot faster for data retrieval even accounting for the population of the table.
April 25, 2006 at 6:41 am
Isn't this exactly why full-text search was invented??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply