子串和CHARINDEX

Question

我需要将“目标输液率：###”放入它自己的字段中。

我正在尝试这个代码子字符串(summaryline，CHARINDEX('Infusion：'，summaryline)，+14)，但是它不工作。

, Initial Rate:  40, Goal Rate of Infusion:  0, to goal rate in 8 hours
, Initial Rate:  30, Goal Rate of Infusion:  0, to goal rate in 8 hours
, Initial Rate:  10, Goal Rate of Infusion:  10, to goal rate in 8 hours, HOLD FOR N/V
, Initial Rate:  40, Goal Rate of Infusion:  10, to goal rate in 8 hours
, Initial Rate:  40, Goal Rate of Infusion:  10, to goal rate in 8 hours
, Initial Rate:  100, Goal Rate of Infusion:  100, to goal rate in 8 hours
, Initial Rate:  15, Goal Rate of Infusion:  15, to goal rate in 8 hours
, Initial Rate:  40, Goal Rate of Infusion:  15, to goal rate in 8 hours

Answer 1

SELECT
    SummaryLine,
    StartIdx,
    EndIdx,
    CONVERT(
        int,
        SUBSTRING( SummaryLine, StartIdx, EndIdx - StartIdx )
    ) AS InfusionRate
FROM (
    SELECT
        SummaryLine,
        StartIdx,
        CHARINDEX(',', summaryLine, StartIdx) AS EndIdx
    FROM (
        SELECT
            SummaryLine,
            CHARINDEX('Infusion:', SummaryLine) + 10 AS StartIdx,
        FROM
            SourceTable
    )
)

或作为单行：

SELECT
    CONVERT(
        int,
        SUBSTRING(
            SummaryLine,
            CHARINDEX('Infusion:', summaryLine) + 10,
            CHARINDEX(
                ',',
                SummaryLine,
                CHARINDEX('Infusion:', summaryLine) + 10
            )
        )
    )
FROM
    SourceTable

不幸的是，SQL不允许您在单个SELECT语句中缓存或别名表达式结果-您需要使用子查询(第一个示例)或重复表达式(第二个示例)。

更新:我做了一些研究，你可以使用CROSS APPLY计算StartIdx只有一次：

SELECT
    SummaryLine,
    CONVERT(
        int,
        SUBSTRING(
            SummaryLine,
            StartIdx,
            CHARINDEX(',', summaryLine, StartIdx) - StartIdx
        )
    ) AS InfusionRate
FROM
    SourceTable
    CROSS APPLY (
        SELECT CHARINDEX('Infusion:', summaryLine) + 10 AS StartIdx
    )