# 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) > 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

• 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. – paparazzo Apr 30 '18 at 16:16

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

• 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. – Dick Kusleika Apr 30 '18 at 19:09