title: How do I find and remove an email token from a string of emails in T-SQL?
body: <p>I'm trying to standardize the email address in a field. Here are a couple examples of the problem I'm trying to solve: </p>
  
  <pre><code>ex 1 yyyyyyyy.xxxxxxxxx.p4elr874@carbuyingemail.com;yyyyyyyy.xxxxxxxxx.daojffab@carbuyingemail.com;xxxxxxxxx@aol.com
  ex 2 xxxxxx51@gmail.com;yyyyyyyyy.xxxxxx.t78m45be@truecarcustomer.com;yyyyyyyyy.xxxxxx.dn4m9ujo@truecarcustomer.com
  ex 3 3xxxxx996@anon.cargurus.com;xxxxxxxxxxx@comcast.net;3xx68xxxx@anon.cargurus.com;4xx2x3xxx@anon.cargurus.com
  </code></pre>
  
  <p>`
  I'm trying to extract the usable email from the auto generated emails in strings similar to this, so for example in</p>
  
  <p>(ex 1) I would want the @aol.com address;<br>
  in (ex 2) I would want the @gmail.com address;<br>
  in (ex 3) I would want the @comcast.net address.   </p>
  
  <p>I tried finding the charindex of the most popular service providers using a case statement, but since I don't how many chars exist before the @ symbol I'm at a loss about how to use the substring method. </p>
  
  <p>There can be anywhere from 2 to 7 emails in a string. I know how to do it for 2. That was easy, but after that I have no idea. Here is what I have so far:</p>
  
  <pre><code>select email, len(email) - len(replace(email,';','')) num_semi
  into #tmp1
  from staging_transformations
  where email != ''  
  order by len(email) - len(replace(email,';','')) desc
  
  
  select email,
  case
      when num_semi = 1 then
          case
              when SUBSTRING(email,0,charindex(';',email)) like '%@anon.cargurus.com' then 
                  case
                      when SUBSTRING(email,charindex(';',email) + 1,len(email)) like '%@anon.cargurus.com' then NULL
                      else SUBSTRING(email,charindex(';',email) + 1,len(email))
                  end
              when SUBSTRING(email,0,charindex(';',email)) like '%@geico.zagdealer.com' then 
                  case
                      when SUBSTRING(email,charindex(';',email) + 1,len(email)) like '%@geico.zagdealer.com' then NULL
                      else SUBSTRING(email,charindex(';',email) + 1,len(email))
                  end
              when SUBSTRING(email,0,charindex(';',email)) like '%@truecarcustomer.com' then 
                  case 
                      when SUBSTRING(email,charindex(';',email) + 1,len(email)) like '%@truecarcustomer.com' then NULL
                      else SUBSTRING(email,charindex(';',email) + 1,len(email))
                  end
              when SUBSTRING(email,0,charindex(';',email)) like '%@carbuyingemail.com' then 
                  case
                      when SUBSTRING(email,charindex(';',email) + 1,len(email)) like '%@carbuyingemail.com' then NULL
                      else SUBSTRING(email,charindex(';',email) + 1,len(email))
                  end
              else SUBSTRING(email,0,charindex(';',email))
          end
      else email
  end test,
  num_semi
  from #tmp1
  </code></pre>
  
  <p>I'm using T-SQL. Any suggestions?</p>