title: I need an SQL query to find all the words you can make with a set of letters, including up to two blank tiles
body: <p>I have a database table called dictionary currently with the following fields for all dictionary entries:</p>
  
  <pre><code>public static final String COLUMN_NAME_UID = "_id_";
  public static final String COLUMN_NAME_WORD = "word";
  public static final String COLUMN_NAME_WORD = "wordSorted";
  public static final String COLUMN_NAME_WORD_LENGTH = "length";
  public static final String COLUMN_NAME_COUNT_A = "count_A";
  public static final String COLUMN_NAME_COUNT_B = "count_B";
  public static final String COLUMN_NAME_COUNT_C = "count_C";
  public static final String COLUMN_NAME_COUNT_D = "count_D";
  public static final String COLUMN_NAME_COUNT_E = "count_E";
  public static final String COLUMN_NAME_COUNT_F = "count_F";
  public static final String COLUMN_NAME_COUNT_G = "count_G";
  public static final String COLUMN_NAME_COUNT_H = "count_H";
  public static final String COLUMN_NAME_COUNT_I = "count_I";
  public static final String COLUMN_NAME_COUNT_J = "count_J";
  public static final String COLUMN_NAME_COUNT_K = "count_K";
  public static final String COLUMN_NAME_COUNT_L = "count_L";
  public static final String COLUMN_NAME_COUNT_M = "count_M";
  public static final String COLUMN_NAME_COUNT_N = "count_N";
  public static final String COLUMN_NAME_COUNT_O = "count_O";
  public static final String COLUMN_NAME_COUNT_P = "count_P";
  public static final String COLUMN_NAME_COUNT_Q = "count_Q";
  public static final String COLUMN_NAME_COUNT_R = "count_R";
  public static final String COLUMN_NAME_COUNT_S = "count_S";
  public static final String COLUMN_NAME_COUNT_T = "count_T";
  public static final String COLUMN_NAME_COUNT_U = "count_U";
  public static final String COLUMN_NAME_COUNT_V = "count_V";
  public static final String COLUMN_NAME_COUNT_W = "count_W";
  public static final String COLUMN_NAME_COUNT_X = "count_X";
  public static final String COLUMN_NAME_COUNT_Y = "count_Y";
  public static final String COLUMN_NAME_COUNT_Z = "count_Z";
  </code></pre>
  
  <p>I want to be able to search for instance test* and find all the words that can be made out of "t" "e" "s" "t" and an wildcard, such as words like "tests" (s is wildcard), "setts" (s is a wildcard), "set", "tet" "es" "te", "best" (b is a wildcard), etc... Anything you can make with any combination of those letters.</p>
  
  <p>I have tried methods like this, but this example only finds the four letter words without a wildcard:</p>
  
  <pre><code>SELECT * FROM dictionary WHERE 
  
  count_E=1 AND
  count_S=1 AND
  count_T=2 
  
  SELECT * FROM dictionary WHERE  length &lt;=4
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code>"137075"    "sett"  "estt"
  "145808"    "stet"  "estt"
  "153675"    "test"  "estt"
  "153851"    "tets"  "estt"
  </code></pre>
  
  <p>Now, I know, this is kind of a dicreet math problem at heart. </p>
  
  <p>Here is how I can get all 5 letter words with one blank space and all the letters provided in the last query:</p>
  
  <pre><code>SELECT * FROM dictionary WHERE 
  
  count_E=1 AND
  count_S=1 AND
  count_T=2 
  
  INTERSECT 
  SELECT * FROM dictionary WHERE  length &lt;=5
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>"97705"     "netts" "enstt"
  "137075"    "sett"  "estt"
  "145250"    "state" "aestt"
  "145808"    "stet"  "estt"
  "152303"    "taste" "aestt"
  "152333"    "tates" "aestt"
  "152632"    "teats" "aestt"
  "153361"    "tents" "enstt"
  "153675"    "test"  "estt"
  "153676"    "testa" "aestt"
  "153733"    "testy" "estty"
  "153769"    "teths" "ehstt"
  "153851"    "tets"  "estt"
  "153874"    "texts" "esttx"
  "156575"    "totes" "eostt"
  "157952"    "trets" "erstt"
  "172060"    "yetts" "estty"
  </code></pre>
  
  <p>However, I'd have to go through all iterations of combinations of letters to get all the hiding sub-words in there... Can anyone help me think of a more elegant approach to finding anagrams and sub-words from a query and up to two wildcards? I am also aware that you can use REGEXP in SQL, so that might be a way. I don't know at this point, and I'm taking this problem to the hive...</p>
  
  <p>Is there a query, or series of queries, or intersections, joins, etc... that would help me solve this problem?</p>
  
  <p><strong>UPDATE</strong>
  I think I may have stumbled upon this, but am not sure if it is working correctly. Any help would be appreciated:</p>
  
  <pre><code>SELECT * FROM dictionary WHERE 
  (
  count_E&lt;=1 AND
  count_S&lt;=1 AND
  count_T&lt;=1 
  )
  INTERSECT SELECT * FROM dictionary WHERE length =(count_E+count_S+count_T+1)     ORDER BY length
  </code></pre>
  
  <p>The +1 is to account for one blank space. For two, I'm thinking of just doing a +2, etc... +0 would be just those letters, and anything you can make from them. </p>