Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

SQL 'Fuzzy' (any part of string matches) match on passed text string in SPROC.

I am new to SQL Server. I created a SPROC that is passed parameters, matches then against a SQL view and returns the results.

It works as it is designed but I want to expand the matching logic for passed VarChar parameters.

Here it is:
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[sptblPaymentsPreSelect]    Script Date: 5/9/2017 2:26:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptblPaymentsPreSelect] 
	-- Add the parameters for the stored procedure here
	@MuniCode		int = null, 
	@Payee			nVarchar(30) = null,
	@LB				nVarchar(30) = null,
	@FromPayDate	dateTime = null,   
	@ThruPayDate	dateTime = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

SELECT [Muni], [LotBlock] , [TieBreaker], [PayHdrID], [PayTaxAuthID], [PaymentSourceID], [TransactionTypeID], [TransactionType], 
       [PaymentDate], [CheckNum] ,[PayTypeID], [FullOrPartialPayID], [PayType], [VoucherNum], [VoucherAlpha], [PayerID], 
	   [Payee], [TaxAuthorityID], [PropertyID], [TaxTypeID], [TaxType], [DepositNum], [DepositDate], [TAReceipt], [TAReceiptSeq],
	    [PayAmt], [PostedStatusID], [PostedStatus], [MasterReceiptNum]
	From dbo.vtblPaymentHeaderAndTA_Inquiry
	WHERE [Muni]         =  ISNULL(@MuniCode , Muni) and 
		  [Payee]        =  ISNULL(@Payee , [Payee]) and 
		  [LotBlock]     =  ISNULL(@LB , [LotBlock])  and 
		  [PaymentDate] >=  ISNULL(@FromPayDate , [PaymentDate]) and
		  [PaymentDate] <=  ISNULL(@ThruPayDate , [PaymentDate]) 

	
END

Open in new window


Right now the passed '@Payee' matches against the entire contents of the [Payee] field in the view.  To get a match on 'John Smith' the user needs to pass 'John Smith'.
Is there a way to revise the lookup so that any record that contains the passed string anywhere in the payee field is returned.  So if the user enters 'Smith', 'Smithton Jones', JOhn Smith Joe Smith, Bill Sixsmith will all be returned.  I have always called this a fuzzy match but I'm not sure what the techincal term is.

I would like the match returned regardless of case.  I also would want JOHN SMITH, john smith and/or John Smith returned.

I could do this in MS Access by using the 'Like' keyword or preceding and following the lookup string with '*' asterisks (*smith*).  Anyone know how to accomplish this in SQL Server queries?
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlcktmguy

ASKER

Thanks to both of you.
Huseyin: The statement works for sure.
PatHartman:  Thanks for the heads up on retrieval performance.   You are spot on.  When I first ran the SPROC with the new fuzzy lookup I thought something was wrong.  I had been testing with the '=' up until then and the return was much faster.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
and make sure you have index on PaymentDate and try to use date parameter all the time...

you may force user to select a date or keep it short...
Huseyin: Perfect, thanks you very much for the follow up.  I need all the help I can get at this point.

I revised the statements:
	From dbo.vtblPaymentHeaderAndTA_Inquiry
	WHERE 
	  ((@MuniCode is null)    or (Muni         = @MuniCode))	AND
	  ((@Payee is null)       or ([Payee]      like '%' + @Payee + '%')) and
	  ((@LB is null)          or ([LotBlock]   like '%' + @LB + '%')) and  
          ((@FromPayDate is null) or ([PaymentDate] >= @FromPayDate))	AND
	  ((@ThruPayDate is null) or ([PaymentDate] <= @ThruPayDate))

Open in new window


and made sure the index on PaymentDate' was in place.

I got the original code in a response to an earlier question about optional parameters in a SPROC.
this way, when you pass null for any parameter, the filter just returns true without need to check the column...

but in your original code, all rows/columns were evaluated against all conditions...
Thanks You, it is noticeably faster, even with the 'fuzzy' lookup.
Full text indexes could be looked at if you find performance of the wild card lookups becoming an issue.
Thank you Matt, will do.
Matt,  how would full text indexes resolve the speed issue?  I am familiar with how binary searches work against normal numeric or text indexes.  Is this something different.  Indexes can be used with LIKE and wildcards but only when the search string starts with the left most characters you are seeking.  So an index can be used to find Smi* but not *ith.
Matt,  how would full text indexes resolve the speed issue?

it looks too complicated to me, and should not turned on/installed unless it is really necessary...

I guess what sql does is, to split all text into words, put into some table, index them and use that table to locate the records when you are tryin to search on those columns...

maybe some strange syntax should be used to use of full text search...

more info here

https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search

https://technet.microsoft.com/en-us/library/ms142547(v=sql.105).aspx

it is available on these editions / add on / option

Enterprise
Business Intelligence
Standard
Web
Express with Advanced Services
If it does index each word, which is what I suspected it would do, that doesn't help with a wild card search.
Thanks to all for your help.