title: How to Use PATINDEX or LIKE to Include Wildcards OR Beginning/End of Line?
body: <p>I'm wondering if there is a way to avoid performing a case statement or having an additional WHERE criteria when isolating a specific pattern, say &quot; <strong>[A-Z][A-Z]</strong> &quot; which can be located either at the start of, middle of, or end of a varchar field.</p>
  <p>It seems as though you should be able to place the wildcard (%) inside a bracket to create additional criteria?</p>
  <h2>The Question:</h2>
  <p>Does anyone have an efficient/clean solution for how to get PATINDEX/LIKE to include both the wildcard symbol AND the beginning/end of a line in it's results?</p>
  <h2>My specific example</h2>
  <p>In my specific instance I have a set of messy city/state/zip address information.  I'm writing a query to pull out just the state abbreviations.  My attempts have been successful except in instances where the city or zip code are missing because of the wildcards in my PATINDEX.</p>
  <pre><code>with cte as 
  (
  select 1 as ID,'Town, FL 00012' as Address1 union
  select 2      ,'Town GA, 00024'             union
  select 3      ,'TX 00048'                   union
  select 4      ,'Town, CA'
  )
  select SUBSTRING(address1,nullif(1+PATINDEX('%[ ,][A-Z][A-Z][ ,]%',address1),1),2) as State_Abbrev
        ,case when Address1 like '[A-Z][A-Z][ ,]%' and Address1 not like '%[ ,][A-Z][A-Z][ ,]%' then SUBSTRING(address1,         PATINDEX('[A-Z][A-Z][ ,]%',address1),2)
              when Address1 like '%[ ,][A-Z][A-Z]' and Address1 not like '%[ ,][A-Z][A-Z][ ,]%' then SUBSTRING(address1,nullif(1+PATINDEX('%[ ,][A-Z][A-Z]',address1),1),2)
              else SUBSTRING(address1,nullif(1+PATINDEX('%[ ,][A-Z][A-Z][ ,]%',address1),1),2)
          end as State_Complex
        ,ID,Address1
    from cte
  </code></pre>
  <p>Which my State_Abbrev alias returns...</p>
  <blockquote>
  <p>FL</p>
  <p>GA</p>
  <p>NULL</p>
  <p>NULL</p>
  </blockquote>
  <p>and my State_Complex returns...</p>
  <blockquote>
  <p>FL</p>
  <p>GA</p>
  <p>TX</p>
  <p>CA</p>
  </blockquote>
  <p>You can see my criteria works for the top two records but I have no way to isolate for the bottom two without creating the ridiculous case statement as you see in my State_Complex field.</p>
  <p>As an aside, yes I'm aware my substring doesn't take into account all possible scenarios (like if the string accidentally has a street address like RD or LN in it) but I understand this.</p>