title: How to quickly find match with different string lengths
body: <p>I've been trying to come up with some psuedo logic to figure out the following.</p>
  
  <p>I have a table with about a hundred thousand records</p>
  
  <p>The primary key is the ID such as</p>
  
  <pre><code>ID, col1, col2, col3
  
  A3242X,,,
  B323X,,,
  A3242Y,,,
  Y32YXX,,,
  </code></pre>
  
  <p>Now I have user input such as   <code>A3242XABC123</code> and <code>Y32</code> and ... and ...</p>
  
  <p>My output is to figure out all records in the table where 
  the ID in the table begins with any of the input values  OR
  any of the input values begin with any values in the Id table.</p>
  
  <p>In this case the output would be </p>
  
  <pre><code>A3242X  (ID A3242XABC123 begins with this)     and 
  
  Y32YXX  (the input Y32 begins with this).  
  </code></pre>
  
  <p>My thoughts are that I would loop thru the input values, compare and then subtract a char from end....such as</p>
  
  <pre><code>search_var=A3242XABC123;
  
  Loop
  
    select id from table where value = search_var;
  
    if found exit;
  
    search_var = substr(search_var, 0,length(search_var-1));
  
  end;
  </code></pre>
  
  <p>--Basically loop thru until find value.</p>
  
  <p>--For second scenario I think I can just do somethign like</p>
  
  <pre><code>search_var[1] = A3242XABC123;
  
  search_var[2] = Y32YXX;
  
  for i = 1 to 2 loop
  
  select id from table where id begins search_var;
  
  add id to result array;
  
  end;
  </code></pre>
  
  <p>I'm looking for an efficient way to do this.  In most cases the user will only enter maybe up to a few dozen ID's.</p>