Parse numbers from text based on relative position to certain substrings The input is a product code. I want to pull out the "percentage" portion of that code. For example, if the code contains the substring BIO, I want all the numbers that follow it until I hit another non-numeric character. You can see from the PATINDEXs the other situations - it probably doesn't add much typing them in English.

Here's the tests:

Product          Returns
DS2BIO5          5%
DS2BIO20WB       20%
DS2BIO2RED7      2%
DS2BIO11         11%
E75              75%
E85              85%
NL10ET87         10%
MG15ET89         15%


I don't have performance issues, mainly because my data set is pretty small. I just don't think I'm competent when it comes to string manipulation in T-SQL and I'd like to use best practices. Also, if I had another condition I would have another IF nesting and I'm already at my limit for what I can tolerate.

ALTER FUNCTION dbo.fnExpectedBlend
    (
        @Item VARCHAR(30)
    )
RETURNS NUMERIC(15, 6)
AS
    BEGIN
        DECLARE @Pos INT

        IF PATINDEX('%BIO[0-9]%', @Item) &gt; 0
            BEGIN
                SET @Pos = PATINDEX('%BIO[0-9]%', @Item) + 3
                SET @Item = SUBSTRING(@Item, @Pos, LEN(@Item))
                SET @Pos = PATINDEX('%[^0-9]%', @Item)
                IF @Pos &gt; 1
                    SET @Item = SUBSTRING(@Item, 1, @Pos - 1)

            END
        ELSE
            BEGIN
                IF PATINDEX('E[0-9][0-9]', @Item) &gt; 0
                    SET @Item = SUBSTRING(@Item, 2, LEN(@Item))
                ELSE
                    BEGIN
                        IF PATINDEX('%[0-9]ET[0-9]%', @Item) &gt; 0
                            SET @Item = SUBSTRING(@Item, 3, 2)
                        ELSE
                            SET @Item = '0'
                    END
            END

        RETURN ISNULL(CAST(@Item AS NUMERIC(15,6)) / 100, 0)
    END
As long as there are just a small number of patterns (and they hardly change) you can use a CASE to find the start position of the digits you want to extract and then simply extract the following n characters if they're digits:

CREATE FUNCTION fnExpectedBlend
    (
        @Item VARCHAR(30)
    )
RETURNS NUMERIC(15, 6)
AS
    BEGIN
        DECLARE @Pos INT

        SET @Pos =
        CASE-- PATINDEX plus length of search string
           WHEN PATINDEX('%BIO[0-9]%', @Item) &gt; 0 THEN PATINDEX('%BIO[0-9]%', @Item) +3 -- PATINDEX plus length of search string
           WHEN PATINDEX('E[0-9]'    , @Item) &gt; 0 THEN PATINDEX('E[0-9]'    , @Item) +1
           WHEN PATINDEX('NL[0-9]%'  , @Item) &gt; 0 THEN PATINDEX('NL[0-9]%'  , @Item) +2
           WHEN PATINDEX('MG[0-9]%'  , @Item) &gt; 0 THEN PATINDEX('MG[0-9]%'  , @Item) +2
           -- easy to extend with additional codes
        END;

    -- assuming there's up to 2 digits, otherwise add another WHEN
    RETURN ISNULL(CAST(CASE WHEN SUBSTRING(@Item, @Pos, 2) LIKE '[0-9][0-9]' 
                            THEN SUBSTRING(@Item, @Pos, 2)
                            ELSE SUBSTRING(@Item, @Pos, 1) -- first character is always a digit
                       END AS NUMERIC(15,6)) / 100, 0)
END