StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is an answer that avoid cursors. I think this might be best broken up into several functions, but tell me what you think and if it is faster for you.</p> <p>For me, here are the performance comparison as a percentage of your original (FWIW): <br/> CPU: 47% Reads: 28% </p> <p>So it does appear to be faster and with fewer reads, plus it works with all your test cases provided.</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 @FullNames1 TABLE(Name varchar(200), SemiColon int, [Space] int) DECLARE @FullNames2 TABLE(Name varchar(200), SemiColon int, [Space] int) INSERT INTO @FullNames1 SELECT item, CHARINDEX(';', item,0) as SemiColon, CHARINDEX(' ', item,0) as [Space] FROM dbo.Split(@Subj1, ',') INSERT INTO @FullNames2 SELECT item, CHARINDEX(';', item,0) as SemiColon, CHARINDEX(' ', item,0) as [Space] FROM dbo.Split(@Subj2, ',') DECLARE @Names1 TABLE(FirstName varchar(100), LastName varchar(100)) DECLARE @Names2 TABLE(FirstName varchar(100), LastName varchar(100)) INSERT INTO @Names1 SELECT CASE WHEN SemiColon <> 0 THEN SUBSTRING(FullNames.Name,SemiColon+1, LEN(FullNames.Name)) ELSE CASE WHEN FullNames.Space <> 0 THEN SUBSTRING(FullNames.Name, 1, FullNames.Space-1) ELSE '' END END AS FirstName, CASE WHEN SemiColon <> 0 THEN SUBSTRING(FullNames.Name, 1, SemiColon-1) ELSE CASE WHEN FullNames.Space <> 0 THEN SUBSTRING(FullNames.Name,FullNames.Space+1, LEN(FullNames.Name)) ELSE FullNames.Name END END AS LastName FROM @FullNames1 FullNames INSERT INTO @Names2 SELECT CASE WHEN SemiColon <> 0 THEN SUBSTRING(FullNames.Name, SemiColon+1, LEN(FullNames.Name)) ELSE CASE WHEN FullNames.Space <> 0 THEN SUBSTRING(FullNames.Name, 1, FullNames.Space-1) ELSE '' END END AS FirstName, CASE WHEN SemiColon <> 0 THEN SUBSTRING(FullNames.Name, 1, SemiColon-1) ELSE CASE WHEN FullNames.Space <> 0 THEN SUBSTRING(FullNames.Name,FullNames.Space + 1, LEN(FullNames.Name)) ELSE FullNames.Name END END AS LastName FROM @FullNames2 FullNames UPDATE @Names1 SET FirstName = Lower(LTrim(RTrim(FirstName))), LastName = Lower(LTrim(RTrim(LastName))) UPDATE @Names2 SET FirstName = Lower(LTrim(RTrim(FirstName))), LastName = Lower(LTrim(RTrim(LastName))) IF EXISTS( SELECT * FROM @Names1 A INNER JOIN @Names2 B ON A.LastName = B.LastName AND ( (A.FirstName = B.FirstName) OR (LEN(A.FirstName) = 1 AND A.FirstName = LEFT(B.FirstName, 1)) OR (LEN(B.FirstName) = 1 AND B.FirstName = LEFT(A.FirstName, 1)) OR (LEN(A.FirstName) = 0) OR (LEN(B.FirstName) = 0) )) BEGIN RETURN 1 END RETURN 0 END </code></pre>

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