SQL语句 怎么用变量替代相同的SQL语句

SQL语句 如何用变量替代相同的SQL语句

select p.PROJECT_NAME project_name, p.ITEM_NUMBER project_no, c.NAME customer_name, c.CODE customer_no, i1.KEYED_NAME pm, i2.KEYED_NAME pe, i3.KEYED_NAME bd, 

       (case  

			when substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) >= 0 and substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) = 2 then 'MP' 

			when substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) >= 3 and substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) <= 8 then 'NPI'

			when substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) >= 9 and substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) <= 10 then 'ESI'

请问这个SQL语句 中的 substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2)  怎么才能不重复写啊？
上述那样虽然可以但是感觉太冗余了，怎样才能将substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) 替换成一个变量，然后其他地方都引用这个变量了？
谢谢啦各位
------解决方案--------------------
你可以用一个临时表先处理成substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2) 的结果再在最终select中写。
------解决方案--------------------

with t as

(

  select p.PROJECT_NAME project_name, p.ITEM_NUMBER project_no, c.NAME customer_name, c.CODE customer_no, i1.KEYED_NAME pm, i2.KEYED_NAME pe, i3.KEYED_NAME bd, 

    substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2)  as t

  from p join c on ... join i1 on ... join ...

)

select PROJECT_NAME project_name, ITEM_NUMBER project_no, NAME customer_name, CODE customer_no, KEYED_NAME pm, KEYED_NAME pe, KEYED_NAME bd, 

  case when t>10 then null

       when t>=9 then 'ESI'

       when t>=3 then 'MPI'

       when t>=0 then 'MP'

  end

------解决方案--------------------

with t as

(

  select p.PROJECT_NAME project_name, p.ITEM_NUMBER project_no, c.NAME customer_name, c.CODE customer_no, i1.KEYED_NAME pm, i2.KEYED_NAME pe, i3.KEYED_NAME bd, 

    substring(p.PROJECT_STAGE,2,charindex('-',p.PROJECT_STAGE) - 2)  as t

  from p join c on ... join i1 on ... join ...

)

select PROJECT_NAME project_name, ITEM_NUMBER project_no, NAME customer_name, CODE customer_no, KEYED_NAME pm, KEYED_NAME pe, KEYED_NAME bd, 

  case when t>10 then null

       when t>=9 then 'ESI'

       when t>=3 then 'MPI'

       when t>=0 then 'MP'

  end

from t