title: Replace REGEXP_SUBSTR in SQL Server
body: <p>I'm Looking for a way of replacing the use of INSTR(...) and REPLACE(REGEXP_SUBSTR(...)) oracle functions in SQL Server.</p>
  
  <p>Original Oracle:</p>
  
  <pre><code>   SELECT 
        Name,
        CASE
             WHEN SUBSTR (NAME, 1, 2) = 'CG'
             THEN SUBSTR (NAME,INSTR (NAME,'_',1,2)+ 1,LENGTH (NAME))
             ELSE REPLACE (REGEXP_SUBSTR (NAME,'_[^_]+',1,2),'_','')
          END AS OPT,
          CASE
             WHEN SUBSTR (NAME, 1, 2) = 'CG'
             THEN SUBSTR (NAME,INSTR (NAME, '_',1,1) + 1, LENGTH (NAME))
             ELSE REPLACE (REGEXP_SUBSTR (NAME,'_[^_]+',1,1),'_','')
          END as Name2
  </code></pre>
  
  <p><strong>Updated</strong> Example Output:</p>
  
  <pre><code>+---------------------------------+----------------------+------------------------------+
  |              NAME               |         OPT          |            Name2             |
  +---------------------------------+----------------------+------------------------------+
  | AE 344592001H 6186694           | NULL                 | NULL                         |
  | AE_161038002_6044777            | 6044777              | 161038002                    |
  | BC_VIVS_HNB011A_1WAM            | HNB011A              | VIVS                         |
  | BC_56230A_30SP                  | 30SP                 | 56230A                       |
  | CG_3334902_NETWK_ ACTLM_3334912 | NETWK_ ACTLM_3334912 | 3334902_NETWK_ ACTLM_3334912 |
  | CG_3334574_HMO1_CORACT_3334575  | HMO1_CORACT_3334575  | 3334574_HMO1_CORACT_3334575  |
  | CG_3207160_POSC_1502AH_3207161  | POSC_1502AH_3207161  | 3207160_POSC_1502AH_3207161  |
  | UH_141015_RHM                   | RHM                  | 141015                       |
  | UH_127757_RIV                   | RIV                  | 127757                       |
  | UH 523725 RIV                   | NULL                 | NULL                         |
  | BS_W0055785_C500_M0005672       | C500                 | W0055785                     |
  +---------------------------------+----------------------+------------------------------+
  </code></pre>
  
  <p>I tried looking at charindex and patindex but nothing panned out, I think the original regex expression is over my head to begin with.  Any ideas on how I could mimic this logic in sql server?</p>