title: Simplify many like statements
body: <p>I have a list of names that are either a company name or a person's name. I have to manually decide if it's a company or a person. I have looked at each row and used a case to decide. If it's a company a one is placed. If it is a person, a zero is placed. Also, I have to parse the individual names into columns of first and last. There are 20 lines for each whe statements. It seems that pervasive can't handle all the statements. Am I doing this wrong? Is there a simpler way?  </p>
  
  <p>Example</p>
  
  <p><code>Own_name
  Dollar Tree LLC
  Jacob Smith
  New Life Co
  Johnson, Robert</code></p>
  
  <p>Here's a sample of what the SQL looks like: </p>
  
  <p><code>select
      CASE
      WHEN own_name LIKE '%co%' 
          OR own_name LIKE '%LLC%
      THEN 1 
      END AS is_compan, // if the own_name has Co or LLC, place a 1 in a column</code>  </p>
  
  <pre><code>CASE
      WHEN own_name NOT LIKE '%co%' 
      OR own_name NOT LIKE '%LLC%
      THEN 0 
      END AS is_individual, //if the own_name DOES NOT have Co or LLC, place a 0 in a column
  </code></pre>
  
  <p><code>CASE 
      WHEN locate(',', own_name)&gt;0 
      AND own_name NOT LIKE '%co%' 
      AND own_name NOT LIKE '%LLC%' 
      THEN ltrim(left(own_name, locate(',', own_name)-1))</code></p>
  
  <pre><code>   WHEN locate(' ', own_name)&gt;0
     AND own_name NOT LIKE '%co%' 
     AND own_name NOT LIKE '%LLC%'
     THEN SUBSTRING(own_name, locate(' ', own_name) + 1, 8000) //if the own_name has a comma or a space and does not have CO or LLC in the column, parse the name for the last name
     END AS LAST_NAME
  </code></pre>
  
  <p><code>CASE
      WHEN locate(',', own_name)&gt;0 
      AND not locate('-', own_name)&lt;0
      AND own_name NOT LIKE '%co%' 
      AND own_name NOT LIKE '%LLC%' 
     THEN ltrim(left(own_name, locate(',', own_name)-1))</code></p>
  
  <pre><code>   WHEN locate(',', own_name)=0
     AND own_name NOT LIKE '%corp%' 
     AND own_name NOT LIKE '%LLC%' 
     THEN Ltrim(SubString(own_name,1,Isnull(Nullif(locate(' ',own_name),0),1000))) //if the own_name has a comma or a space and does not have CO or LLC in the column, parse the name for the first name
     END AS First_name
  </code></pre>
  
  <p>The result</p>
  
  <p><code>Own_name             is_compan    is individual  Last name     First name
  Dollar Tree LLC       1<br>
  Jacob Smith                        0              Smith         Jacob
  New Life Co           1<br>
  Johnson, Robert                    0              Johnson       Robert</code>   </p>