title: Checking multiple conditions in CASE expression SQL
body: <p>I am trying to parse a free form text with a case expression and I am not able to get the results I am expecting. Can you please suggest if I can achieve this in SQL?</p>
  
  <p>Here I have shown some sample data in the SQL Fiddle link below,  but the city_lookup table has 100s of rows because there are multiple cities in a state. The case statement was just an attempt to put them in appropriate buckets. The free form text can contain multiple keywords which can contain cities from the lookup table(city_lookup) and for every such occurrence, we need to record the visited state of the user. For example if the freeform text contains two cities, I should be able to write the output as two different rows with each city visited, for the same user.</p>
  
  <p>SQL Fiddle Link - <a href="http://sqlfiddle.com/#!7/61ef9" rel="nofollow noreferrer">http://sqlfiddle.com/#!7/61ef9</a></p>
  
  <p>DDL</p>
  
  <pre><code>create table city_lookup(city varchar(50), state varchar(50));
  insert into city_lookup values('dallas', 'texas');
  insert into city_lookup values('austin', 'texas');
  insert into city_lookup values('phoenix', 'arizona');
  insert into city_lookup values('tuscon', 'arizona');
  insert into city_lookup values('fresno', 'california');
  insert into city_lookup values('monterey', 'california');
  
  create table log_cities
  (user_id int, visited_log varchar(512));
  
  INSERT INTO log_cities values(123, 'This user was in dallas also probably in monterey');
  INSERT INTO log_cities values(234, 'Logged: visisted tuscon');
  INSERT INTO log_cities values(456, 'In March she visited texas, austin');
  INSERT INTO log_cities values(567, 'He was probably here in phoenix and austin');
  
  </code></pre>
  
  <p>Query</p>
  
  <pre><code>select 
        user_id,
        case
          when visited_log like '%dallas%' then 'texas'
          when visited_log like '%austin%' then 'texas'
          when visited_log like '%phoenix%' then 'arizona'
          when visited_log like '%tuscon%' then 'arizona'
          when visited_log like '%fresno%' then 'california'
          when visited_log like '%monterey%' then 'california'
          else 'None' end visited_state
  from
        log_cities;
  </code></pre>
  
  <p>Input &amp; Output</p>
  
  <pre><code>-- Actual output
  123     texas
  234     arizona
  456     texas
  567     arizona
  
  -- Actual output expected
  123     texas
  123     california
  234     arizona
  456     texas
  567     arizona
  567     texas
  </code></pre>