title: Improve SQL Function That Searches Table For Matching String
body: <p>I have a function that searches a for a matching ICD-9 code in another table character by character. I am doing this because the format that we receive ICD-9 codes in is not consistent and many of them have extra numbers on the end or they may have a decimal in the wrong place or even missing. </p>
  
  <p>We have no option other than looking for partial matches to at least find the category that the ICD9 falls in. We are not using this for billing purposes, so a partial match is okay. Luckily, ICD9 code are structured and numbered by category. So lets say that we receive a code for 'Lymphosarcoma Involving Intra Abdominal Lymph Node' which is 200.13. We may receive the code as 20013, 20.013, 200.13112, etc..., but I can match the code character by character until I find the closest match, which for all of the examples I provided would be 200.13. </p>
  
  <p>The function works almost 100% correctly, it just takes a very long time to run because has to search the lookup table 4 or 5 times to look for the closest match, which makes the function really slow. Especially when it has to match up to 12 codes per record for 1000's of records.</p>
  
  <p>Any ideas on improving the query?</p>
  
  <pre><code>/*  Find match for an ICD9 code in LookupDiseases and selects the desciption . Return the matching 
      description for the ICD9 code.
  */
  ALTER FUNCTION [dbo].[uf_LookupICD9Desc] (@ClaimA99 UNIQUEIDENTIFIER,@ICD9 NVARCHAR(10))  
  RETURNS NVARCHAR(MAX)
  AS  
  BEGIN
  DECLARE @Substring NVARCHAR (10)
  DECLARE @Description NVARCHAR(MAX) 
  DECLARE @Length INT
  DECLARE @Count INT
  
  /* Remove decimal place from string */
  SET @ICD9 = REPLACE(@ICD9,'.','')
  
  /* Get lenth of string */
  SET @Length = LEN(@ICD9)
  
  /* Initialize count */
  SET @Count = 1
  
  /* Get Substring */
  SET @Substring = SUBSTRING(@ICD9,1,@Count)
  
  /* Start processing */
  IF (@Length &lt; 1 OR @ICD9 IS NULL)
      /* Validate @ICD9 */
      BEGIN
  
          SET @Description = 'No match found for ICD-9.'
  
      END
  ELSE IF ((SELECT COUNT(*) FROM LookupDiseases WHERE REPLACE(LookupCodeDesc,'.','') LIKE @Substring + '%') &lt; 1)
      /* Check for at least one match */
      BEGIN
  
          SET @Description = 'No match found for ICD-9.'
  
      END
  ELSE
      /* Look for matching code */
      BEGIN
  
          WHILE ((SELECT COUNT(*) FROM LookupDiseases WHERE REPLACE(LookupCodeDesc,'.','') LIKE @Substring + '%') &lt;&gt; 1 AND (@Count &lt; @Length + 1))
          BEGIN
  
              /* Update substring value */
              SET @Substring = SUBSTRING(@ICD9,1,@Count + 1)
  
              /* Increment @Count */
              SET @Count += 1
  
              /* Select the first matching code and get description */
              SELECT TOP(1) @Description = LookupName FROM LookupDiseases WHERE REPLACE(LookupCodeDesc,'.','') LIKE @Substring + '%' ORDER BY LookupCodeDesc ASC
  
          END
      END
  
      /* Return the ICD9 code description.  */
      RETURN(@Description)
  END
  </code></pre>