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

Question

Post reply

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

maifs

SSC Veteran

Points: 222
More actions
March 26, 2023 at 12:19 am

#4165068

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.
March 27, 2023 at 5:12 am

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

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
('lasix'), 
('lasixx'), 
('lasixxx'), 
('lasax'), 
('lasi'), 
('lisax')


declare @searchstring varchar(10) = 'lasix'

select string,@searchstring
from #temp
where soundex(string) = soundex(@searchstring)

Michael L John One Orange Chip Points: 27060 More actions · Answer 3

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:
http://www.sqlservercentral.com/articles/61537/

maifs SSC Veteran Points: 222 More actions · Answer 4

Thank you everyone.

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

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

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:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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)