4
\$\begingroup\$

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) > 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 > 1
                    SET @Item = SUBSTRING(@Item, 1, @Pos - 1)

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

        RETURN ISNULL(CAST(@Item AS NUMERIC(15,6)) / 100, 0)
    END
\$\endgroup\$
  • \$\begingroup\$ Maybe something like DECLARE @T table (numLen int, stringBefore varchar(10), stringAfter varchar(10)); insert into @T values (2, '%BIO', '%'), (2, 'E', ''); and the process that in a cursor. \$\endgroup\$ – paparazzo Apr 30 '18 at 16:16
2
\$\begingroup\$

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) > 0 THEN PATINDEX('%BIO[0-9]%', @Item) +3 -- PATINDEX plus length of search string
           WHEN PATINDEX('E[0-9]'    , @Item) > 0 THEN PATINDEX('E[0-9]'    , @Item) +1
           WHEN PATINDEX('NL[0-9]%'  , @Item) > 0 THEN PATINDEX('NL[0-9]%'  , @Item) +2
           WHEN PATINDEX('MG[0-9]%'  , @Item) > 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
\$\endgroup\$
  • \$\begingroup\$ I tried to use CASE but I couldn't get the syntax right. Then I read that it's not a statement, which I read as 'it has to be in a SELECT'. I didn't think about using it in the assignment (I had the assignment within the CASE). Anyway, that's way cleaner than the nested IF. And if I have anymore, it grows vertically and not horizontally. Thanks. \$\endgroup\$ – Dick Kusleika Apr 30 '18 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.