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