Selecting records that match user-entered keywords

• steal

  SSC Eights!

  Points: 827

  More actions

  January 19, 2010 at 7:58 pm

  #209251

  I need to select patient records that match user search criteria. The user can enter multiple search terms, space-delimited, which i parse into a table variable. Search terms can include names, account ids or patient ids. The tricky part is this: If they enter more than one term, ALL terms must match a DIFFERENT patient field. All search terms are to be wild-carded.

  So they might enter 'John Smith', 'smith A425872', j smith', 'j s', you get the idea.

  I have code that works... mostly... It figures out which search term matches which patient field, and makes sure that there are as many matches as there are search terms. I won't bother posting it at this point because it's rather clumsy and doesn't work in the following unlikely, however possible, scenario.

  Say that the user enters 'a a m' as the search terms. Given the following two simplified tables, how do I select Michael but not Maya (my code selects both)?

  declare @tblKeywords table (keyword varchar(10))

  insert into @tblKeywords select 'm'

  insert into @tblKeywords select 'a'

  insert into @tblKeywords select 'a'

  select * from @tblKeywords

  declare @tblPatients table (AccountId varchar(20), PatientId varchar(20), LastName varchar(20), FirstName varchar(20), MiddleName varchar(20))

  insert into @tblPatients select 'p532k123', '7859023473', 'Mendoza', 'Maya', 'Anne'

  insert into @tblPatients select 'a456s032', '7859023473', 'Ayers', 'Michael', 'Harry'

  select * from @tblPatients

  It doesn't seem like this should be as hard as I'm making it. Insight anyone? Thanks!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 19, 2010 at 9:01 pm

  #1105809

  you didn't quite post enough information...

  you said Say that the user enters 'a a m' as the search terms. Given the following two simplified tables, how do I select Michael but not Maya (my code selects both)?

  but you didn't explain how you know the code should NOT find Maya; what is the rule/logic for not finding that value? what is the rule for returning Michael?

  i'm sure if you can explain the rules, we can help refine the resulting sql to get the results.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• steal

  SSC Eights!

  Points: 827

  More actions

  January 19, 2010 at 9:10 pm

  #1105811

  m% (wildcard) must match a patient field (such as Michael or Maya)

  a% must match a DIFFERENT patient field (such as Ayers or Anne)

  a% must match a DIFFERENT patient field (such as a456s032 in Michael's case, but there's not another matching 'a' field for Maya)

  Does this help?

• Jeff Moden

  SSC Guru

  Points: 1004402

  More actions

  January 19, 2010 at 10:28 pm

  #1105833

  Why couldn't you just use whatever you're using to distribute the search terms across columns for exactly what you just wrote above?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)