mlcktmguy
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:
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
you may force user to select a date or keep it short...
ASKER
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:
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.
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))
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...
but in your original code, all rows/columns were evaluated against all conditions...
ASKER
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.
ASKER
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.
ASKER
Thanks to all for your help.
ASKER
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.