使用SUBSTRING() SQL Server 2008-R2从字符串中获取编号

Question

我有些数据如下所示：

AS WRITTEN                                             | UNITS | ROUTINE
TRANSFUSE - RED BLOOD CELLS 1 UNITS ROUTINE, TRANSF... | 1     | 
TRANSFUSE - RED BLOOD CELLS 2UNITS ROUTINE, TRAN...    | 2     |
TRANSFUSE FRESH FROZEN PLASMA 2 ROUTINE                |       | 2
TRANSFUSE - RED BLOOD CELLS 2 UNITS ROUTINE, TRAN...   | 2     |
TRANSFUSE CRYOPRECIPITATE 10 ROUTINE                   |       |    <- 10 SHOULD BE HERE

我使用下面的SELECT语句来获取值，这可能是一种粗糙的方法，但这是我第一次尝试SUBSTRING() CHARINDEX等。

选择语句：

, CASE
    WHEN SUBSTRING(SO.DESC_AS_WRITTEN, CHARINDEX('UNIT', SO.DESC_AS_WRITTEN)-2,1) NOT IN (
        '1','2','3','4','5','6','7','8','9','10','11'
    )
        THEN ''
    ELSE SUBSTRING(SO.DESC_AS_WRITTEN, CHARINDEX('UNIT', SO.DESC_AS_WRITTEN)-2,1)
  END AS [UNITS]
, CASE 
    WHEN SUBSTRING(SO.DESC_AS_WRITTEN, CHARINDEX('ROUTINE', SO.DESC_AS_WRITTEN)-2,1) NOT IN (
        '1','2','3','4','5','6','7','8','9','10','11'
    )
        THEN ''
    ELSE SUBSTRING(SO.DESC_AS_WRITTEN, CHARINDEX('ROUTINE', SO.DESC_AS_WRITTEN)-2,2)
  END AS [ROUTINE]

如前所述，10应该显示在ROUTINE下的最后一栏中，但不是，我也不明白为什么。其他人似乎都工作得很好。

谢谢,

来自@EkriirkE和@GoatCO的大型帮助的更新我使用了以下代码作为解决方案：

,(CAST
    (ISNULL
        (REPLACE
            (REPLACE
                (REPLACE
                    (CASE 
                        WHEN PATINDEX('%[0-9]UNIT%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9]UNIT%',so.desc_as_written)-1,2) 
                        WHEN PATINDEX('%[0-9] UNIT%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9] UNIT%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9]ROUTINE%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9]ROUTINE%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9] ROUTINE%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9] ROUTINE%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9]STAT%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9]STAT%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9] STAT%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9] STAT%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9]TODAY%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9]TODAY%',so.desc_as_written)-1,2)
                        WHEN PATINDEX('%[0-9] TODAY%',so.desc_as_written) > 0 
                            THEN SUBSTRING(so.desc_as_written,
                                 PATINDEX('%[0-9] TODAY%',so.desc_as_written)-1,2) 
                    END,
                'S',''),
            'T',''),
        'U','')
    , 0)
AS INT)
) AS [TEST]

Answer 1

这可能是比较可取的：

SELECT *,CASE WHEN PATINDEX('%[0-9]UNITS%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9]UNITS%',written)-1,2) 
              WHEN PATINDEX('%[0-9] UNITS%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9] UNITS%',written)-1,2) 
         END
        ,CASE WHEN PATINDEX('%[0-9]ROUTINE%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9]ROUTINE%',written)-1,2)
              WHEN PATINDEX('%[0-9] ROUTINE%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9] ROUTINE%',written)-1,2)
         END 
FROM  #Table1

演示：SQL Fiddle

将CASE语句封装在替换中可能比处理获取给定变化的适当字符数更容易，即：

SELECT *,REPLACE(CASE WHEN PATINDEX('%[0-9]UNITS%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9]UNITS%',written)-1,2) 
                      WHEN PATINDEX('%[0-9] UNITS%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9] UNITS%',written)-1,2) 
                 END,'S','')                       
        ,REPLACE(CASE WHEN PATINDEX('%[0-9]ROUTINE%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9]ROUTINE%',written)-1,2)
                      WHEN PATINDEX('%[0-9] ROUTINE%',written) > 0 THEN SUBSTRING(written,PATINDEX('%[0-9] ROUTINE%',written)-1,2)
                 END,'S','') 
FROM  #Table1

Answer 2

在从查找单元或例程中备份2个字符后，您只需要使用1个字符。"10例程“返回"0”，不在您的in列表中。也就是说，您的代码只适用于单数数字。你应该拿-3个字符然后两个字符的长度并修剪空间，但是如果你的样本中确实有“2单位”和“2单位”这样的运行单元，那么你就会遇到更大的困难。