StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL match function written by me taking too much time. Optimization required
text
Body
copied!<p>This is the sample test data for which the function should return 1 for anything else it should return 0:</p> <pre><code>Inventor: Raj Patel Attorney: Raj Patel Inventor: Patel; Raj Attorney: Patel Inventor: Patel; R Attorney: Patel; Raj Inventor: Patel; Raj, Madnani; Raj Attorney: Patel; Raj Inventor: Patel; Raj Attorney: Patel; R **Eg.** Select dbo.Match('Patel; R','Patel; Raj') </code></pre> <p><strong>All these executions should return 1:</strong></p> <pre><code>Select dbo.Match('Raj Patel','Raj Patel') Select dbo.Match('Patel; Raj','Patel') Select dbo.Match('Patel; R',' Patel; Raj') Select dbo.Match('Patel; Raj, Madnani; Raj','Patel; Raj') Select dbo.Match('Patel; Raj','Patel; R') </code></pre> <p>should return 1</p> <p>This is my match function using too many cursors:</p> <pre><code>ALTER FUNCTION [dbo].[Match] ( @Subj1 varchar(8000), @Subj2 varchar(8000) ) RETURNS bit AS BEGIN Set @Subj1 = IsNull(@Subj1,'') Set @Subj2 = IsNull(@Subj2,'') If @Subj1 = '' Or @Subj2 = '' Begin Return 0 End If Lower(@Subj1) = Lower(@Subj2) Begin Return 1 End Declare Subj1NamesCurr Cursor For --all separate names Select * From dbo.Split(@Subj1,',') Declare Subj2NamesCurr Cursor SCROLL For --all separate names Select * From dbo.Split(@Subj2,',') Open Subj1NamesCurr Open Subj2NamesCurr Declare @Sub1Names varchar(8000) Declare @Sub2Names varchar(8000) Declare @Sub1NamePart varchar(8000) Declare @Sub2NamePart varchar(8000) Declare @Sub1PartCount tinyint = 0 Declare @Sub2PartCount tinyint = 0 Declare @Sub1NamesPart TABLE(Data varchar(8000)) Declare @Sub2NamesPart TABLE(Data varchar(8000)) Declare @MatchCount int = 0 Declare @TempCount int = 0 Fetch From Subj1NamesCurr INTO @Sub1Names --fetch 1st name from 1st subject Insert into @Sub1NamesPart Select * From dbo.Split(@Sub1Names,';') --get names part from 1st subject's row Select @Sub1PartCount = Count(*) From @Sub1NamesPart While @@Fetch_Status = 0 --each names of 1st subject Begin Fetch First From Subj2NamesCurr into @Sub2Names While @@Fetch_Status = 0 --each names of 1st subject Begin Declare Sub1NameCurr Cursor For Select * From @Sub1NamesPart --name parts of 1st subject OPEN Sub1NameCurr Fetch From Sub1NameCurr into @Sub1NamePart Insert into @Sub2NamesPart Select * From dbo.Split(@Sub2Names,';') Select @Sub2PartCount = Count(*) From @Sub2NamesPart Set @MatchCount = 0 While @@Fetch_Status = 0 --splitted name of 1st subject Begin Declare Sub2NameCurr Cursor For Select * From @Sub2NamesPart --name parts of 2nd subject OPEN Sub2NameCurr Fetch From Sub2NameCurr into @Sub2NamePart Set @TempCount = 0 While @@Fetch_Status = 0 --splitted name of 2nd subject Begin Set @TempCount = @TempCount + 1 If dbo.Trim(Lower(@Sub1NamePart)) = dbo.Trim(Lower(@Sub2NamePart)) Begin Set @MatchCount = @MatchCount + 1 If @Sub2PartCount = 1 Begin Return 1 End End Else If Lower(Left(dbo.Trim(@Sub1NamePart),1)) = Lower(dbo.Trim(@Sub2NamePart)) Or Lower(Left(dbo.Trim(@Sub2NamePart),1)) = Lower(dbo.Trim(@Sub1NamePart)) Begin Set @MatchCount = @MatchCount + 1 End Fetch Next From Sub2NameCurr into @Sub2NamePart Delete from @Sub2NamesPart Insert into @Sub2NamesPart Select * From dbo.Split(@Sub2Names,';') End If @MatchCount = @Sub2PartCount Begin Return 1 End CLOSE Sub2NameCurr DEALLOCATE Sub2NameCurr Fetch Next From Sub1NameCurr into @Sub1NamePart Delete from @Sub1NamesPart Insert into @Sub1NamesPart Select * From dbo.Split(@Sub1Names,';') --get names part from 1st subject's row Select @Sub1PartCount = Count(*) From @Sub1NamesPart End CLOSE Sub1NameCurr DEALLOCATE Sub1NameCurr End End Close Subj1NamesCurr Deallocate Subj1NamesCurr Close Subj2NamesCurr Deallocate Subj2NamesCurr Return 0 END </code></pre> <p><strong>Edit:</strong> To create no confusions, Trim is just a function that does a LTrim and RTrim over your string. That's about it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload