从公式的除数中提取单引号之间的字符串部分

Question

select ATTRFormula,
       Devider_col=substring(attrformula,charindex('/',ATTRFormula)+9,len(attrformula)-2)
from temp_mst_measure 
where attrtype='derived' 
and attrformula like '%/%'

例：

DMFunc('Food_01')/DMFunc('BTP_01')，这是阿特公式。

BTP_01')，我是Devider_col

如何在')中删除最后两个字符Devider_col

Answer 1

用REPLACE替换空字符串。

select ATTRFormula,
       Devider_col=
        REPLACE(
            substring(attrformula,charindex('/',ATTRFormula)+9,len(attrformula)-2),
            ''')',
            '')
from temp_mst_measure 
where attrtype='derived' 
and attrformula like '%/%'

Answer 2

试试这个：

select substring(AttrFormula, [start], [end] - [start]) from (
    select AttrFormula, [start], charindex('''', AttrFormula, [start] + 1) [end] from (
        select AttrFormula, charindex('''', AttrFormula, charindex('/', AttrFormula, 1)) + 1 [start]
        from MY_TABLE
    ) a
) a