title: Generating a character-type pattern of a field
body: <p>I want to look at the character content of a field, then produce a count of character-type patterns in that field. So a value of 'SomeStuff 123' would have a pattern of 'XXXXXXXXX 999'. </p>
  
  <p>I have a couple of ways of doing this already but they seem long and clumsy. I'd like to find the shortest/most elegant way of doing this. </p>
  
  <p>I'm thinking PATINDEX might be the way to go:</p>
  
  <pre><code>Select 
      Pattern = 
      Case 
          When PatIndex('%[0-9]%', Left([MyField], 1)) = 1 Then '9' 
          When PatIndex('%[a-Z]%', Left([MyField], 1)) = 1 Then 'X' 
          When PatIndex('% %', Left([MyField], 1)) = 1 Then ' '
          Else '?'
      End 
  + 
      Case
          When PatIndex('%[0-9]%', Substring([MyField], 2, 1)) = 1 Then '9' 
          When PatIndex('%[a-Z]%', Substring([MyField], 2, 1)) = 1 Then 'X' 
          When PatIndex('% %', Substring([MyField], 2, 1)) = 1 Then ' ' 
          Else '?'
      End 
  --...
  </code></pre>
  
  <p>Here are a couple of ways I have tested which work:</p>
  
  <p>Literal REPLACE method:</p>
  
  <pre><code>Select [MyFieldPattern] = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([MyField], 'A','X') ,'B','X') ,'C','X') ,'D','X') ,'E','X') ,'F','X') ,'G','X') ,'H','X') ,'I','X') ,'J','X') ,'K','X') ,'L','X') ,'M','X') ,'N','X') ,'O','X') ,'P','X') ,'Q','X') ,'R','X') ,'S','X') ,'T','X') ,'U','X') ,'V','X') ,'W','X') ,'Y','X') ,'Z','X') ,'0','1') ,'2','1') ,'3','1') ,'4','1') ,'5','1') ,'6','1') ,'7','1') ,'8','1') ,'9','1') 
  , Count(*) As Counts
   From [MyDatabase]
  Group By 
  Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([MyField], 'A','X') ,'B','X') ,'C','X') ,'D','X') ,'E','X') ,'F','X') ,'G','X') ,'H','X') ,'I','X') ,'J','X') ,'K','X') ,'L','X') ,'M','X') ,'N','X') ,'O','X') ,'P','X') ,'Q','X') ,'R','X') ,'S','X') ,'T','X') ,'U','X') ,'V','X') ,'W','X') ,'Y','X') ,'Z','X') ,'0','1') ,'2','1') ,'3','1') ,'4','1') ,'5','1') ,'6','1') ,'7','1') ,'8','1') ,'9','1') 
  Order By Count(*) Desc
  </code></pre>
  
  <p>Brute force CASE method:</p>
  
  <pre><code>Select
      FieldName_Analyzed =        Convert(VarChar(15), 'MyField'),
      Literal_Value_Aggregated =  Convert(VarChar(30), 
            Convert(VarChar(1), Case When ASCII(Substring([MyField], 1, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 1, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 1, 1)) = 32 Then '_' When ASCII(Substring([MyField], 1, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 2, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 2, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 2, 1)) = 32 Then '_' When ASCII(Substring([MyField], 2, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 3, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 3, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 3, 1)) = 32 Then '_' When ASCII(Substring([MyField], 3, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 4, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 4, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 4, 1)) = 32 Then '_' When ASCII(Substring([MyField], 4, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 5, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 5, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 5, 1)) = 32 Then '_' When ASCII(Substring([MyField], 5, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 6, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 6, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 6, 1)) = 32 Then '_' When ASCII(Substring([MyField], 6, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 7, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 7, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 7, 1)) = 72 Then '_' When ASCII(Substring([MyField], 7, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 8, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 8, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 8, 1)) = 82 Then '_' When ASCII(Substring([MyField], 8, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 9, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 9, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 9, 1)) = 92 Then '_' When ASCII(Substring([MyField], 9, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 10, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 10, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 10, 1)) = 102 Then '_' When ASCII(Substring([MyField], 10, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 11, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 11, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 11, 1)) = 112 Then '_' When ASCII(Substring([MyField], 11, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 12, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 12, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 12, 1)) = 122 Then '_' When ASCII(Substring([MyField], 12, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 13, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 13, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 13, 1)) = 132 Then '_' When ASCII(Substring([MyField], 13, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 14, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 14, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 14, 1)) = 142 Then '_' When ASCII(Substring([MyField], 14, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 15, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 15, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 15, 1)) = 152 Then '_' When ASCII(Substring([MyField], 15, 1)) Is Null Then Char(216) Else '?' End)
          ),
      Percent_Populated =         Convert(Decimal(5,2), Convert(Decimal(5,4), Convert(Float, Count(
            Convert(VarChar(1), Case When ASCII(Substring([MyField], 1, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 1, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 1, 1)) = 32 Then '_' When ASCII(Substring([MyField], 1, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 2, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 2, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 2, 1)) = 32 Then '_' When ASCII(Substring([MyField], 2, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 3, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 3, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 3, 1)) = 32 Then '_' When ASCII(Substring([MyField], 3, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 4, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 4, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 4, 1)) = 32 Then '_' When ASCII(Substring([MyField], 4, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 5, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 5, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 5, 1)) = 32 Then '_' When ASCII(Substring([MyField], 5, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 6, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 6, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 6, 1)) = 32 Then '_' When ASCII(Substring([MyField], 6, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 7, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 7, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 7, 1)) = 72 Then '_' When ASCII(Substring([MyField], 7, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 8, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 8, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 8, 1)) = 82 Then '_' When ASCII(Substring([MyField], 8, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 9, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 9, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 9, 1)) = 92 Then '_' When ASCII(Substring([MyField], 9, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 10, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 10, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 10, 1)) = 102 Then '_' When ASCII(Substring([MyField], 10, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 11, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 11, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 11, 1)) = 112 Then '_' When ASCII(Substring([MyField], 11, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 12, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 12, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 12, 1)) = 122 Then '_' When ASCII(Substring([MyField], 12, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 13, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 13, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 13, 1)) = 132 Then '_' When ASCII(Substring([MyField], 13, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 14, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 14, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 14, 1)) = 142 Then '_' When ASCII(Substring([MyField], 14, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 15, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 15, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 15, 1)) = 152 Then '_' When ASCII(Substring([MyField], 15, 1)) Is Null Then Char(216) Else '?' End)
          ))/(Select Count(*) From [MyDatabase]))*100),
      Total_Count =               Convert(Int, Count(
            Convert(VarChar(1), Case When ASCII(Substring([MyField], 1, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 1, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 1, 1)) = 32 Then '_' When ASCII(Substring([MyField], 1, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 2, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 2, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 2, 1)) = 32 Then '_' When ASCII(Substring([MyField], 2, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 3, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 3, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 3, 1)) = 32 Then '_' When ASCII(Substring([MyField], 3, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 4, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 4, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 4, 1)) = 32 Then '_' When ASCII(Substring([MyField], 4, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 5, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 5, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 5, 1)) = 32 Then '_' When ASCII(Substring([MyField], 5, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 6, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 6, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 6, 1)) = 32 Then '_' When ASCII(Substring([MyField], 6, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 7, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 7, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 7, 1)) = 72 Then '_' When ASCII(Substring([MyField], 7, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 8, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 8, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 8, 1)) = 82 Then '_' When ASCII(Substring([MyField], 8, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 9, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 9, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 9, 1)) = 92 Then '_' When ASCII(Substring([MyField], 9, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 10, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 10, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 10, 1)) = 102 Then '_' When ASCII(Substring([MyField], 10, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 11, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 11, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 11, 1)) = 112 Then '_' When ASCII(Substring([MyField], 11, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 12, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 12, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 12, 1)) = 122 Then '_' When ASCII(Substring([MyField], 12, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 13, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 13, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 13, 1)) = 132 Then '_' When ASCII(Substring([MyField], 13, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 14, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 14, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 14, 1)) = 142 Then '_' When ASCII(Substring([MyField], 14, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 15, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 15, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 15, 1)) = 152 Then '_' When ASCII(Substring([MyField], 15, 1)) Is Null Then Char(216) Else '?' End)
          ))
      From [MyDatabase]
      Group By 
            Convert(VarChar(1), Case When ASCII(Substring([MyField], 1, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 1, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 1, 1)) = 32 Then '_' When ASCII(Substring([MyField], 1, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 2, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 2, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 2, 1)) = 32 Then '_' When ASCII(Substring([MyField], 2, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 3, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 3, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 3, 1)) = 32 Then '_' When ASCII(Substring([MyField], 3, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 4, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 4, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 4, 1)) = 32 Then '_' When ASCII(Substring([MyField], 4, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 5, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 5, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 5, 1)) = 32 Then '_' When ASCII(Substring([MyField], 5, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 6, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 6, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 6, 1)) = 32 Then '_' When ASCII(Substring([MyField], 6, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 7, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 7, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 7, 1)) = 72 Then '_' When ASCII(Substring([MyField], 7, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 8, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 8, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 8, 1)) = 82 Then '_' When ASCII(Substring([MyField], 8, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 9, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 9, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 9, 1)) = 92 Then '_' When ASCII(Substring([MyField], 9, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 10, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 10, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 10, 1)) = 102 Then '_' When ASCII(Substring([MyField], 10, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 11, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 11, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 11, 1)) = 112 Then '_' When ASCII(Substring([MyField], 11, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 12, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 12, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 12, 1)) = 122 Then '_' When ASCII(Substring([MyField], 12, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 13, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 13, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 13, 1)) = 132 Then '_' When ASCII(Substring([MyField], 13, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 14, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 14, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 14, 1)) = 142 Then '_' When ASCII(Substring([MyField], 14, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 15, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 15, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 15, 1)) = 152 Then '_' When ASCII(Substring([MyField], 15, 1)) Is Null Then Char(216) Else '?' End)
      Order By
            Convert(VarChar(1), Case When ASCII(Substring([MyField], 1, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 1, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 1, 1)) = 32 Then '_' When ASCII(Substring([MyField], 1, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 2, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 2, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 2, 1)) = 32 Then '_' When ASCII(Substring([MyField], 2, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 3, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 3, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 3, 1)) = 32 Then '_' When ASCII(Substring([MyField], 3, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 4, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 4, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 4, 1)) = 32 Then '_' When ASCII(Substring([MyField], 4, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 5, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 5, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 5, 1)) = 32 Then '_' When ASCII(Substring([MyField], 5, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 6, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 6, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 6, 1)) = 32 Then '_' When ASCII(Substring([MyField], 6, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 7, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 7, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 7, 1)) = 72 Then '_' When ASCII(Substring([MyField], 7, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 8, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 8, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 8, 1)) = 82 Then '_' When ASCII(Substring([MyField], 8, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 9, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 9, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 9, 1)) = 92 Then '_' When ASCII(Substring([MyField], 9, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 10, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 10, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 10, 1)) = 102 Then '_' When ASCII(Substring([MyField], 10, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 11, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 11, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 11, 1)) = 112 Then '_' When ASCII(Substring([MyField], 11, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 12, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 12, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 12, 1)) = 122 Then '_' When ASCII(Substring([MyField], 12, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 13, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 13, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 13, 1)) = 132 Then '_' When ASCII(Substring([MyField], 13, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 14, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 14, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 14, 1)) = 142 Then '_' When ASCII(Substring([MyField], 14, 1)) Is Null Then Char(216) Else '?' End)
          + Convert(VarChar(1), Case When ASCII(Substring([MyField], 15, 1)) Between 48 And 57 Then '9' When ASCII(Substring([MyField], 15, 1)) Between 65 And 122 Then 'X' When ASCII(Substring([MyField], 15, 1)) = 152 Then '_' When ASCII(Substring([MyField], 15, 1)) Is Null Then Char(216) Else '?' End)
      Desc
  </code></pre>