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

Question

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]

EkriirkE · Accepted Answer · 2014-01-08 20:57:30Z

2

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

edited Jan 8, 2014 at 20:57

answered Jan 8, 2014 at 20:15

EkriirkE

2,32121 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MCP_infiltrator Over a year ago

Thank you much, this did work out well, I am not to worried about the ruuing-ins as they have yet to happen but it is a good point and something for me to consider.

Hart CO Over a year ago

@MCP_infiltrator you have such a run-in in your example 2UNITS

EkriirkE Over a year ago

@Goat CO That's why I brought it up... Welcome!

Hart CO · Accepted Answer · 2014-01-08 20:53:09Z

1

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

edited Jan 8, 2014 at 20:53

answered Jan 8, 2014 at 20:33

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

5 Comments

MCP_infiltrator Over a year ago

Thank you for this, I have to play around with it as I am getting the following error conversion failed when converting the varchar value 's1' to data type int.

Hart CO Over a year ago

@MCP_infiltrator it could be that you have a situation where there's no space like CELLS1 wrapping these in a REPLACE() statement might be easiest way to fix that.

MCP_infiltrator Over a year ago

I'll have to do some reading on REPLACE() as your answer is my first encounter with it. Thank you for the help

Hart CO Over a year ago

@MCP_infiltrator Added replace to answer, and they can be nested if you have multiple characters to replace, ie: REPLACE(REPLACE(CASE ... END, 'S',''),'U','') would replace any S or U that's returned in the CASE statement with nothing ''.

MCP_infiltrator Over a year ago

I have chosen to make this answer as the accepted, both of you helped me out a lot, I just find this to be a more robust answer while it also introduces me to some new functions.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related