how to write a query by using some filter patterns in SQL Server?

  • what I want is to apply some filter patterns to search the data. For example. There is a large physical table in the database regarding medicines, I want to allow search even if user write lasix, lasixx, lasixxx, lasax, lasi, lisax. Data should be retrieved for the medicine lasix. How can I write these filter patterns in SQL query? And this query should be compilent with old SQL server versions like 2008 as well. I want to do it in SQL Server side instead of c# logic. Help and assistance is really appreciated.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • SOUNDEX is going to be the closest thing in T-SQL to do it, but it wouldn't match LASIX to LASI as the sound isn't the same.

    Just note it's non-SARGable so will cripple your performance.

    Best this is done in C# / Application code.

    create table #temp (string varchar(10))
    insert into #temp values

    declare @searchstring varchar(10) = 'lasix'

    select string,@searchstring
    from #temp
    where soundex(string) = soundex(@searchstring)
  • If this is a user search that's coming from a UI, can you create a translation table?

    What about full text search?


    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

  • Thank you everyone.

    Yes it is coming through UI and user can enter anything in it

  • Ok, then can this be a pick list? Eliminate the human error.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:

  • Everybody wants to search for things like Google does.  Go buy a product that will do this for you in SQL server... it'll be cheaper that you trying to build one that doesn't actually work well.   Some will recommend full text search (built into sql server) and (ugh!) SOUNDEX and you can try those but, ultimately, I think that's going to lead to disappointment.  Go buy a product to do it.

    --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)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply