title: SQL PATINDEX patterns for filtering
body: <p>I am trying to filter a string to be used for as a subject line for Outlook mail.  I use a filter b/c some strings that have special characters will convert a subject line into Unicode which I want to prevent.  I am trying to use PATINDEX but can not get brackets into the pattern, does anyone know how to do this and/or have a good site to share on a guide to creating patterns for PATINDEX. Here is what I have:</p>
  
  <pre><code>-- First replaces special characters with similar safe ones.
  -- Finally excludes all other characters not specified in the pattern.
  -- =============================================
  
  ALTER Function [dbo].[RemoveSpecialCharacters](@Temp VarChar(1000))
  Returns VarChar(1000)
  AS
  BEGIN
      DECLARE @KeepValues as varchar(50)
  
      SET @Temp = Replace(Replace(Replace(Replace(Replace(Replace(Replace(@Temp,'”','"'),'“','"'),'‘','`'),'’','`'),'–','-'),'[','('),']',')')
      SET @KeepValues = '%[^a-z0-9()."`:;,#&amp;+*\/-]%'
  
      WHILE PatIndex(@KeepValues, @Temp) &gt; 0
          SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
  
      RETURN @Temp
  END
  </code></pre>
  
  <p>As you can see since I could not figure out how to get brackets in the pattern, for now I'm replacing them with parenthesis.</p>