title: MySQL Variable Substitution
body: <p>I want to simplify the following using dynamic SQL like one could do in Transact SQL.</p>
  
  <p>I want to do something like:</p>
  
  <pre><code>SET @s = replace(field_name, '_complete','')
  </code></pre>
  
  <p>and use @s instead of replace(field_name, '_complete','')</p>
  
  <p>Please adive if possible and if so how.</p>
  
  <p>My current code:</p>
  
  <pre><code> select distinct    
  if(instr(replace(field_name, '_complete',''),'_') &lt;= 5
            ,left(replace(field_name, '_complete','')
                 ,instr(replace(field_name, '_complete',''),'_') - 1
                )  
            ,replace(field_name, '_complete','')    
          ) AS form_id   ,replace(
          if(instr(replace(field_name, '_complete',''),'_') &lt;= 5,
              mid(replace(field_name, '_complete',''),
                  instr(replace(field_name, '_complete',''),'_') + 1,
                  length(replace(field_name, '_complete','')) - instr(replace(field_name, '_complete',''),'_')
                 )
              ,replace(field_name, '_complete','')
            ),
          '_',
          ' ') as form_name     ,field_name     from redcap_extract2use    where field_name like '%_complete'   order by 1;
  </code></pre>
  
  <p>The above would then be replaced with:</p>
  
  <pre><code>select distinct 
    if(instr(@s,'_') &lt;= 5 ,left(@s,instr(@s,'_') - 1),@s ) AS form_id
    ,replace( if(instr(@s,'_') &lt;= 5,
          mid(@s,instr(@s,'_') + 1,length(@s) - instr(@s,'_')),@s), '_',  ' ') as form_name  
    ,field_name  
    from redcap_extract2use 
    where field_name like '%_complete'
    order by 1;
  </code></pre>
  
  <p>and I would have an execute... to run the query</p>