How to search for cases that have a specific phrase or word within a results field.
LIMS Administrators, Database Administrators
How can I find all cases that have a specific word in the results?
Using the AD-HOC query, how do I find all cases that have a specific word in the entered results?
How do I search through the REPTEXT field in the REPTEXT table?
At times, LIMS administrators are given the task of finding all cases that contain a specific word that may not be bound to a specific field within a given table.
REPTEXT.reptext is a memo field and the contents can only be searched using SQL Server Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL2005 +).
A district attorney is working a case that had a disputed sentence or word listed in the final report results. The district attorney has noticed a pattern of cases where this particular phrase had been included in the final reports for several cases. The district attorney asks the lab to get a general count of cases that had the disputed phrase or words.
The LIMS administrator receives the request from the DA and notices that he/she cannot search LIMS using the AD HOC query since the phrase in question is not being populated in a specific field. Since the phrase is buried within the results entered into the General Analytical Module, the LIMS administrator senses that a query will need to be done within the database itself as opposed to searching within the AD HOC Query tool within LIMS.
Final reports that pull results entered in to the General Analytical Module are placed into the REPTEXT table. Within the REPTEXT table there is a column called Reptext. This column in the database refers to the results field that analysts users see when entering results within the General Analytical Module. The information entered into this result field can vary widely depending on the case and how the General Analytical Module is used.
This field (REPTEXT.reptext) is a memo field and the contents can only be searched using SQL Server Query Analyzer (SQL Server 2000) or SQL Server Management Studio (SQL 2005 +).
To find a particular word or phrase within the reptext column, the following query can be executed against the LIMS database.
select * from reptext
where reptext like '%Insert Phrase or Word%'
For greater efficiency in completing a full text search, you can use SQL Server's full-text search capabilities.
A full-text catalogue must be created using SQL Server Enterprise Manager on the REPTEXT.reptext column.
A full-text index must be placed on the REPTEXT table using this catalogue.
The catalogue must be populated and a schedule defined to keep it up to date as new results are added.
For more information on enabling full text searching in SQL or Oracle, please contact your Database Administrator to see if this function has been setup and/or configured.
If a full text catalogue has been created, a SQL query could be run to provide a list of request IDs, case id’s and result types for requests with the target reptext string.
SELECT REQ_ID, CASE_ID, RESULT_TYPE
WHERE Reptext like '%Insert Phrase or Word%'