title: substring in sql server 2008 to upload to another table while checking for empty string
body: <p>I've been trying to figure this out for a couple days but haven't been able to do so. I think I know what needs to be done, but couldn't figure out how to do it. Here's the description of what I want to do: </p>
  
  <p>sample of data: </p>
  
  <pre><code>Status  Eff Date    End Date    CardHldrID Name GCNSeqNo Product Name 
  DENIED  05/29/2014  08/26/2014  111111111  H K  069923   LINACLOTIDE : 290 MCG  
  DENIED  05/29/2014  05/28/2015  222222222  S T  042993   ZALEPLON : 5 MG  
  DENIED  05/29/2014  06/27/2014  333333333  P S  063511   NEBIVOLOL HCL : 10 MG  
  DENIED  05/29/2014  05/28/2015  444444444  S F  060333   RANOLAZINE : 500 MG  
  DENIED  05/29/2014  11/24/2014  555555555  H E  065344   LIRAGLUTIDE : 0.6MG/0.1  
  DENIED  05/29/2014  05/28/2015  666666666  P T  051786   ROSUVASTATIN CALCIUM : 40 MG 
  DENIED  05/29/2014  05/28/2015  777777777  K S  066295   RIFAXIMIN : 550 MG  
  DENIED  05/29/2014  05/28/2015  888888888  H C  034731   INSULIN LISPRO : 100/ML   
  </code></pre>
  
  <p>This is how the data is scrapped from the vendor's website for our internal business purpose. I'm not much worried about the other parts of the data currently, but the GCNSeqNo and Product Name. Since GCNSeqNo contains multiple product name for the same strength, I want to create a drug table with drug_id (identity), gcn_seq_no, and strength. I also want to create products table with prod_id and product_name. So far, I have extracted unique gcn_seq_no from the actual database that contains 60,000 to the new drug table. Then, I wrote a query that patches the product name with the same gcn_seq_no and similar name but without strength. </p>
  
  <p>For example: in the database, the gcn_seq_no '034731' may have product name as INSULIN LISPRO : 100/ML in one record, but may have just INSULIN LISPRO or INSULIN in other records even though its the same thing, but it may also have a different name that may be a brand name (B, for instance). The query I wrote makes sure the naming is consistent; it makes all product name with the same gcn_seq_no INSULIN LISPRO : 100/ML if it starts with INSULIN, but since B is a brandname it leaves it intact. Then it uploads just the product name without duplicates, i.e., name before ":" to products table. </p>
  
  <p>Now the issue that I'm having is that I want to extract the strength 100 M/L (after ":") from the same product name. Here's the query: </p>
  
  <pre><code>update pa_denial_letters.dbo.drugs set pa_denial_letters.dbo.drugs.strength =
  ( case
      when len(s1.strength) &lt;&gt; 0 or s1.strength &lt;&gt; '' or s1.strength is not null
      then s1.strength
    end
  )
  from (select prod_name, gcn_seq_no, 
        Case
          when  CHARINDEX(':', PROD_NAME) &lt;&gt; 0 
              then substring(PROD_NAME, CHARINDEX(':', prod_name)+1, len(PROD_NAME)) 
              --else substring(prod_name, 0,0)
        end as strength from pa_claims1) as s1
        where pa_denial_letters.dbo.drugs.gcn_seq = s1.gcn_seq_no;
  </code></pre>
  
  <p>With this query, what it seems is that as long as the product name has the same name and contains strength, it extracts it and uploads it in the drugs table. But, if there are two different product names for the same gcn_seq_no in which one contains the strength the other one doesn't, the query updates the database even if the strength is empty. I tried so many different things but either I get error or just doesn't work. It seems like I may have to join the table to itself and check if the strength is '', but I couldn't even figure out where to join. I'd really appreciate if someone can help. </p>
  
  <p>EDIT: </p>
  
  <p>Here's another thing I tried: </p>
  
  <pre><code>update pa_claims3 set pa_claims3.prod_name = pa_claims3.prod_name + substring(s1.NEWNAME, CHARINDEX(':', s1.NEWNAME)+1, len(s1.NEWNAME))
  from
  (select P1.PROD_NAME as NAME, P1.GCN_SEQ_NO as GCN, P2.PROD_NAME as NEWNAME
        from pa_claims3 as P1 
        inner join pa_claims3 as P2 
        on P1.GCN_SEQ_NO = P2.GCN_SEQ_NO
        and P2.PROD_NAME not like P1.PROD_NAME + '%'
        AND CHARINDEX(':', P2.PROD_NAME) &lt;&gt; 0
        and CHARINDEX(':',P1.PROD_NAME) = 0
  
  ) as s1
   where pa_claims3.PROD_NAME not like s1.name and pa_claims3.GCN_SEQ_NO = s1.GCN
  </code></pre>
  
  <p>This query is supposed to check the product name in the database and if they have same gcn_seq_no but doesn't contain the strength, then it's supposed to copy the strength from one name and update it in the other. Instead, it's copying onto itself where the strength is appearing as "5 MG 5 MG". I'm trying this to ensure the strength is never empty even if the product names are different within the same GCN_SEQ_NO since each GCN_SEQ_NO must have strength.</p>
  
  <p>Thanks</p>