CODEWITHSUNDEEP
sqlsql-server-2008substring
MCP_infiltrator
MCP_infiltrator

Reputation: 4179

Get Number from string using SUBSTRING() SQL Server 2008-R2

I have some data that looks like the following:

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

I am using the following SELECT statement to obtain the values, which might be a crude way of doing, but this is my first foray into SUBSTRING() CHARINDEX ETC.

SELECT statement:

, 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]

As stated above 10 should be showing in the last column under ROUTINE but is not and I do not understand why. All of the others are seem to be working just fine.

Thank you,

UPDATE From the large help from @EkriirkE and @GoatCO I have used the following code as the solution:

,(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]

Upvotes: 0

Views: 1640

Answers (2)

EkriirkE
EkriirkE

Reputation: 2295

You are taking only 1 char after backing up 2 chars from finding UNIT or ROUTINE. "10 ROUTINE" returns "0" which is not in your IN list. i.e. Your code only works for single-digit numbers. You ought to take -3 chars then 2 chars length and trim the spaces, but then you run into more difficulty if you indeed have running-ins like "2UNITS" vs " 2 UNITS" in your sample

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

This might be preferable:

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

Demo: SQL Fiddle

It might be easiest to wrap the CASE statement inside of a replace than to deal with grabbing the proper number of characters given variations, ie:

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

Upvotes: 1

Related Questions